topic Re: How to calculate variable value from variable name saved in table. in QlikView

How to calculate variable value from variable name saved in table.

Thu, 19 Feb 2015 08:22:41 GMT

Hi ,

I have created two variables 'aa' and 'bb' as follows in my application:

aa = sum({$<Name={'b'}>}Value)-sum({$<Name={'a'}>}Value)

bb = sum({$<Name={'b'}>}Value)+sum({$<Name={'a'}>}Value)

my table is

LOAD * INLINE [

Name, Value, Type,TEST_F

a, 10,,

b, 25,,

c, , F,aa

d,,F,bb

];

In this Table Type ='F' means it is formula and now I want to calculate the values of c and d as variables mentioned aa and bb respectively

so the final output should be

Name	Value
a	10
b	25
c	15
d	35

in table I used the following expression and it is working fine

if(Type='F',

if(Name='c',aa,if(Name='d',bb))

,sum(Value))

but Now I do not want to use If condition as there can be more calculations required in future , for the same reason I have used TEST_F value of c and d as formula names.

I want to use something as :

if(Type='F',

TEST_F

,sum(Value))

Can you please help me out ?

Thanks

Manu

Re: How to calculate variable value from variable name saved in table.

Thu, 19 Feb 2015 08:34:48 GMT

Hi Manu,

Can u try this :

if(Type='F',

$(=$(=only(TEST_F)))

,sum(Value)

)

Re: How to calculate variable value from variable name saved in table.

Thu, 19 Feb 2015 08:50:14 GMT

No Ashish,

It is not working.

Re: How to calculate variable value from variable name saved in table.

its_anandrjs — Thu, 19 Feb 2015 09:02:51 GMT

Hi,

If you calculate this way what happen by loading the individual values for aa and bb and then load into variable and then load into again in the table by conditions and you have to use the if condition in the variable because SET is not worked in the variables.

Tab1:
LOAD * INLINE [
Name, Value, Type, TEST_F
a, 10
b, 25
c, , F, aa
d, , F, bb
];

Values:
LOAD
Sum(if(Name='b',Value)) - Sum(if(Name='a',Value)) as aa,
Sum(if(Name='b',Value)) + Sum(if(Name='a',Value)) as bb
Resident Tab1;

Let aa = Peek('aa',0,'Values');
Let bb = Peek('bb',0,'Values');

NoConcatenate
New:
LOAD
Name, Value, Type,
if(Type='F',
if(Name='c','$(aa)',if(Name='d','$(bb)')),Value) as TESTF_Values
Resident Tab1;
DROP Table Tab1;

Regards

Anand

Re: How to calculate variable value from variable name saved in table.

Sun, 22 Feb 2015 12:02:30 GMT

Hi,

Can u try this ...

Fill other TEST_F fields with some temporary variable. Declare that variable and assign some dummy value.

In my case inline table is like :

LOAD * INLINE [

Name, Value, Type,TEST_F

a, 10,,temp

b, 25,,temp

c, , F,aa

d,,F,bb

];

i have declared variable temp = 0;

now we can use this expression :

=if(Type='F',

$(=$(=maxString(TEST_F)))

sum(Value)

)

Please try it tell me if it is working ...

Re: How to calculate variable value from variable name saved in table.

Mon, 23 Feb 2015 07:23:15 GMT

Hi Ashish,

I tried this but it is showing one value in all the rows.

Thanks

Manu

Re: How to calculate variable value from variable name saved in table.

Mon, 23 Feb 2015 07:24:06 GMT

Thanks Anand but I want to use without if statement.