pass the field name into expression using variable

[jeevays7]

Hi All,

I have two tables like below

Table 1:

Date	NAME	Base_Curr	Value
1	A	USD	10
2	B	EUR	10
3	C	USD	20

Table 2:

Date	EUR	USD
1	20	10
2	10	50
3	30	20

the above table are linked with Date.

now i need the below Result:

Date	NAME	Base_Curr	Value
1	A	USD	100
2	B	EUR	100
3	C	USD	400

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:

Date	NAME	Base_Curr	Value	explaination	ex
1	A	USD	100	here base currecy is USD so USD values will come ie.10) from table 2	10*10
2	B	EUR	100	here base currecy is EUR so EUR value will come ie.10) from table 2	10*10
3	C	USD	400	here base currecy is USD so USD values will come ie.20) from table 2	20*20

[MK_QSL]

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]

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

[jeevays7]

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]

1) I don't understand what you have explained

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

Drop Field Key;

[jeevays7]

Thank you Manish,

[jeevays7]

Can we pass field name by using variable?

[jeevays7]

Manish,

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

[Anonymous]

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 · Accepted solution]

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
1	A	USD	100
2	B	EUR	100
3	C	USD	400

Cheers

Andrew