FIELD VALUE as part of Variable Name on SCRIPT

[#1]

Hi @ll,

I've a doubt when trying to do the following on script. Let's show first my scenario.

Table1:
LOAD * INLINE [
ID, INTRIM_CALC, SALES, Country, RATE
1, 1, 250, SP, 1.3
2, 2, 350, SP, 1.1
3, 2, 450, ES, 1.2
4, 1, 550, ES, 1.4
];

Then i declare two variables :

SET vIntrim_1 = 'SALES*RATE';
SET vIntrim_2 = 'SALES/RATE';

Now i want to generate a new field based on the evaluation of the variable depending on the value of the field INTRIM_CALC. It should be something like show below:

LOAD *,
$(vIntrim_[INTRIM_CALC]) as INTRIM_CALC_EVALUATED
Resident Table1;

But i can't find the way to handle it

. I know i can use IF's, or variables with parameters or Peek function, but i'm treating a very large amount of variables and a very huge # of rows in the fact table.

Anyone knows how to catch the value of INTRIM_CALC field for each row, place it as suffix for my var prefix, and then Evaluate it?

The result should be as shown :

Thanks in advance,

King Regards,

Albert

*App attached

[#2]

=$($(=('vIntrim_'&INTRIM_CALC)))

THIS IS WORKING AT CHART LEVEL BUT NOT AT LOAD SCRIPT. I NEED THIS

[jagan]

HI,

Try like this

LOAD *,
If(INTRIM_CALC = 1, SALES * RATE, SALES/RATE as INTRIM_CALC_EVALUATED
Resident Table1;

Hope this helps you.

[jagan]

Hi,

Try this using variable

Table1:

LOAD * INLINE [

    ID, INTRIM_CALC, SALES, Country, RATE

    1, 1, 250, SP, 1

    2, 2, 350, SP, 3

    3, 2, 450, ES, 5

    4, 1, 550, ES, 2

];

SET vIntrim_1 = SALES*RATE;

SET vIntrim_2 = SALES/RATE;

Fact:

LOAD *,

  // $(vIntrim_[INTRIM_CALC]) as INTRIM_CALC_EVALUATED

  if(INTRIM_CALC = '1', Evaluate($(vIntrim_1)), Evaluate($(vIntrim_2))) as INTRIM_CALC_EVALUATED

Resident Table1;

drop table Table1;

Regards,

Jagan.

[#5]

Hi Jagan, thanks for the response.

i know i can do that, in fact, it's done in the qvw. But i need a workaround without using IF's or Peek's.

Thanks

Albert