Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner
Partner

pass the field name into expression using variable

Hi All,

I have two tables like below

Table 1:

  

DateNAMEBase_CurrValue
1AUSD10
2BEUR10
3CUSD20

Table 2:

DateEURUSD
12010
21050
33020

the above table are linked with Date.

now i need the below Result:

  

DateNAMEBase_CurrValue
1AUSD100
2BEUR100
3CUSD400

Here im using below expression:

SUM(Value*(vCurrecy))


inside the variable i have the expression

if(Base_Curr='EUR','EUR','USD')



Explanation of above result table:

  

DateNAMEBase_CurrValueexplainationex
1AUSD100here base currecy is USD so USD values will come ie.10) from table 210*10
2BEUR100here base currecy is EUR so EUR value will come ie.10) from table 210*10
3CUSD400here base currecy is USD so USD values will come ie.20) from table 220*20
1 Solution

Accepted Solutions
Honored Contributor

Re: pass the field name into expression using variable

Hi Jeremy,

Try:

Rates: 

Load * Inline  [ 

Date, EUR, USD 

1, 20, 10 

2, 10, 50 

3, 30, 20 

]; 

 

 

Values:  

Load * Inline [ 

Date, NAME, Base_Curr, Value 

1, A, USD, 10 

2, B, EUR, 10 

3, C, USD, 20 

]; 

Date NAME Base_Curr Sum(Value*Pick(Match(Base_Curr,'USD','EUR'), USD,EUR))
600
1AUSD100
2BEUR100
3CUSD400

Cheers

Andrew

View solution in original post

12 Replies
Highlighted
MVP
MVP

Re: pass the field name into expression using variable

Use below script.. I think rest of the things easy from here.. You can use Value * CurrencyValue

CrossTable(Base_Curr,Cur)

TempT2:

Load * Inline

[

Date, EUR, USD

1, 20, 10

2, 10, 50

3, 30, 20

];

T2:

Mapping Load Date & Base_Curr as Key, Cur Resident TempT2;

Drop Table TempT2;

T1:

Load

*,

ApplyMap('T2',Date & Base_Curr) as CurrencyValue

Inline

[

Date, NAME, Base_Curr, Value

1, A, USD, 10

2, B, EUR, 10

3, C, USD, 20

];

Highlighted
MVP & Luminary
MVP & Luminary

Re: pass the field name into expression using variable

Hi,

Try  below code.

Table1:

LOad *,Date&Base_Curr as Key inline [ 

Date,NAME,Base_Curr,Value

1,A,USD,10

2,B,EUR,10

3,C,USD,20

];

Table2:

CrossTable(Curr,CurrVal)

Load * inline [

Date,EUR,USD

1,20,10

2,10,50

3,30,20

];

Left join(Table1)

Load Date&Curr as Key,CurrVal

Resident Table2;

Drop table Table2;

NOw create a chart with dimension like Date,Base_Curr,Name

And expression as Sum(Value*CurrVal)

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Partner
Partner

Re: pass the field name into expression using variable

Hi Manish,

I don't need keys. because i have one level before name. so

Branch Name

A          A

A          B

A is USD and B is EUR,

so when we select A in branch, Name's all values should multiple with USD only,

if we select a name(i.e A) in Name field, that particular currency value should be multiplied.

How it is working?

Highlighted
MVP
MVP

Re: pass the field name into expression using variable

1) I don't understand what you have explained

2) You can add one more line to my previous code.

Drop Field Key;

Highlighted
Partner
Partner

Re: pass the field name into expression using variable

Thank you Manish,

Highlighted
Partner
Partner

Re: pass the field name into expression using variable

Can we pass field name by using variable?

Highlighted
Partner
Partner

Re: pass the field name into expression using variable

Manish,

Can we do the same thing without Date&currency key??

Highlighted
Valued Contributor II

Re: pass the field name into expression using variable

Try:

SET vCurrency='if(Base_Curr='EUR'['EUR],[USD])';

Or on the frontend just enter with no equal sign, I think.

Then in your expression use a $ sign expansion like

=SUM(Value*$(vCurrency))

Honored Contributor

Re: pass the field name into expression using variable

Hi Jeremy,

Try:

Rates: 

Load * Inline  [ 

Date, EUR, USD 

1, 20, 10 

2, 10, 50 

3, 30, 20 

]; 

 

 

Values:  

Load * Inline [ 

Date, NAME, Base_Curr, Value 

1, A, USD, 10 

2, B, EUR, 10 

3, C, USD, 20 

]; 

Date NAME Base_Curr Sum(Value*Pick(Match(Base_Curr,'USD','EUR'), USD,EUR))
600
1AUSD100
2BEUR100
3CUSD400

Cheers

Andrew

View solution in original post