Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
];
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
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?
1) I don't understand what you have explained
2) You can add one more line to my previous code.
Drop Field Key;
Thank you Manish,
Can we pass field name by using variable?
Manish,
Can we do the same thing without Date¤cy key??
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))
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