String Comparison Error

[DNA_DC]

Hello,

I am trying to run this code to compare a piece of text to select a particular calculation based on the "option" selected by the user but keep getting this error:

Error on Line 4: Syntax error on or before ['Calculation Option
Logical expression

Here is the code I am running

Code: Select all

FEEDSTRINGS;
Skipcheck;

['Required $' ] = IF(['Calculation Option'] @= 'FTE', ['Average Cost']*['Amount' ], ['Amount' ]);

['Required FTE'] = IF(['Calculation Option'] @= '$', ['Amount' ]/['Average Cost'], ['Amount' ]);

Feeders;

['Average Cost', 'Amount', 'Calculation Option'] => ['Required $', 'Required FTE'];

As always, any help is greatly appreciated.

[Gregor Koch]

Hi

Try the DB() reference rather than [] syntax.
If that doesn't help make sure the two elements are String Elements.

In your feeder section try
['Amount'] => ['Required $'];
['Amount'] => ['Required FTE'];

which should be sufficient feeders.

Cheers

[Alan Kirk]

Hello,

I am trying to run this code to compare a piece of text to select a particular calculation based on the "option" selected by the user but keep getting this error:

Error on Line 4: Syntax error on or before ['Calculation Option
Logical expression

Here is the code I am running

Code: Select all

FEEDSTRINGS;
Skipcheck;

['Required $' ] = IF(['Calculation Option'] @= 'FTE', ['Average Cost']*['Amount' ], ['Amount' ]);

['Required FTE'] = IF(['Calculation Option'] @= '$', ['Amount' ]/['Average Cost'], ['Amount' ]);

Feeders;

['Average Cost', 'Amount', 'Calculation Option'] => ['Required $', 'Required FTE'];

As always, any help is greatly appreciated.

I can't find a reference for this, but I seem to dimly recall that you need to use the DB() function when you're doing a lookup of a string value like that. I just looked at my primary string cube, and for any rules I've always used the DB() function to pull values from the cube into an If() function. I'm guessing that I had a good reason for it at the time, even if I can't currently recall what it was...

[Gregor Koch]

Beat you to it, Alan.

[Alan Kirk]

Try the DB() reference rather than [] syntax.
If that doesn't help make sure the two elements are String Elements.

You beat me to it; I had typed out the reply but was holding off hitting [Send] until I could find a reference, then discovered that I couldn't. You don't happen to recall where that's documented do you? I've been trying to find it and it's driving me batty... I'm sure I've seen it somewhere, unless I'm just remembering prior posts in this Forum or its predecessor.

(Of course, the possibility of something being buried in some obscure location in the documentation, or completely absent from it, wouldn't be a first.)

[Alan Kirk]

Beat you to it, Alan.

Yup, e-mails out of the Forum seem to be a touch tardy today; I didn't see this reply before I posted my previous one.

[Michel Zijlema]

Hi,

The problem with the posted rules are maybe (partially) caused because 'Average Cost', 'Amount', 'Calculation Option', 'Required $' and 'Required FTE' are all elements in the same dimension?
In this case the feeder is syntactically not correct. This problem will be solved by using the feeders Gregor posted (as ['amount'] is sufficient as a feeder). I also don't think you need the FEEDSTRINGS; on top, as the values you're calculating are numeric.

Michel

[paulsimon]

Hi

I am guessing that all the items that you are feeding from are in the same dimension, presumbably your measures dimension? Assuming that I am right (and if not, then please post the structure of your cube), then if you want to feed from more than one thing in the same dimension then you need { } around the elements. However, in this case you only need to feed from Amount, so your feeder should be:

[ 'Amount' ] => [ {'Required $', 'Required FTE'} ];

Though personally I would write it as:

[ 'Amount' ] => [ 'Required $' ] ;
[ 'Amount' ] => [ 'Required FTE' ];

The reason is that you only need to know if Amount is non-zero to determine if there is a non-zero result that needs consolidating.

I think that you are getting the syntax error because Calculation Option is a string element and you are using it to feed a numeric element.

Regards


Paul Simon

Hello,

I am trying to run this code to compare a piece of text to select a particular calculation based on the "option" selected by the user but keep getting this error:

Error on Line 4: Syntax error on or before ['Calculation Option
Logical expression

Here is the code I am running

Code: Select all

FEEDSTRINGS;
Skipcheck;

['Required $' ] = IF(['Calculation Option'] @= 'FTE', ['Average Cost']*['Amount' ], ['Amount' ]);

['Required FTE'] = IF(['Calculation Option'] @= '$', ['Amount' ]/['Average Cost'], ['Amount' ]);

Feeders;

['Average Cost', 'Amount', 'Calculation Option'] => ['Required $', 'Required FTE'];

As always, any help is greatly appreciated.

[Martin Ryan]

[ 'Amount' ] => [ 'Required $' ] ;
[ 'Amount' ] => [ 'Required FTE' ];

To add to the options, you can also write

Code: Select all

['Amount'] =>  [ 'Required $' ],  [ 'Required FTE' ];

which I find quite useful and clear as a compromise between Paul's two options.

Martin

[paulsimon]

Hi

To correct my own post, I had forgotten that you can only use curly brackets on the left hand side of a rule so

[ 'Amount' ] => [ {'Required $', 'Required FTE'} ];

won't work

So you need to go with my other suggestion

[ 'Amount' ] => [ 'Required $' ] ;
[ 'Amount' ] => [ 'Required FTE' ];

or Martin's

[ 'Amount' ] => [ 'Required $' ] , [ 'Required FTE' ] ;

Just for completeness, the other way is to create a consolidation of [ 'Required $' ] plus [ 'Required FTE' ] and feed that since feeding a consolidation automatically feeds all elements below it. However depending on the meaning of these measures a consolidation might look a little odd. Where I have consolidation purely for feeding I generally name them something like zFeed Measures.

Regards


Paul Simon