Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
Help me please.
I have a list of Accounts and need to calculate expression, based on conditions. Conditions for each Account are different.
Example:
Table1:
Account | Condition |
---|---|
Account1 | 'Dim1' |
Account2 | 'Dim2' |
Account3 | 'Dim1','Dim2' |
Account4 | 'Dim2',''Dim3' |
Table2:
Dim | Value |
---|---|
Dim1 | 10 |
Dim2 | 20 |
Dim3 | 30 |
Condition is a set of Dims.
Accounts is Dimension in PivotTable.
I'm writing such Expression:
Sum({<Dim = {$(=Condition)}>}Value)
It doesn't work.
I tryed to use advice from this post Re: How to add Different Expressions for Different rows of a table ???
I'm making a variable Exp = "Dim1", "Dim2","Dim1,Dim2", "Dim2,Dim3", a field row = RowNo() and writing such expression:
Sum({<Dim = {$(=Pick(row, $(Exp)))}>}Value).
It works, only if I'm changing row to a specific value (1 or 2).
So field row is not divided on dimension.
If you have any ideas, help please.
Thank you!
maybe in the load script
Table1:
load * inline [
Account, Condition
Account1, "Dim1"
Account2 ,"Dim2"
Account3 ,"Dim1,Dim2"
Account4 ,"Dim2,Dim3"
];
Table2:
load * inline [
Dim, Value
Dim1, 10
Dim2 ,20
Dim3, 30
];
link:
load Account, subfield(Condition, ',') as Dim Resident Table1;
See attached qvw.
it works perfectly.
Thank you.