Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jeevays7
Partner - Creator III
Partner - Creator III

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
effinty2112
Master
Master

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
MK_QSL
MVP
MVP

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

];

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
jeevays7
Partner - Creator III
Partner - Creator III
Author

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?

MK_QSL
MVP
MVP

1) I don't understand what you have explained

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

Drop Field Key;

jeevays7
Partner - Creator III
Partner - Creator III
Author

Thank you Manish,

jeevays7
Partner - Creator III
Partner - Creator III
Author

Can we pass field name by using variable?

jeevays7
Partner - Creator III
Partner - Creator III
Author

Manish,

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

Anonymous
Not applicable

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))

effinty2112
Master
Master

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