Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Here is my data set
Aux:
LOAD * INLINE [
ID, Amount1, Type
1, 100, A
1, 130, B
2, 230, A
2, 130, A
2, 140, B
3, 100, A
3, 200, A
3, 300, A
3, 500, A
4, 200, A
4, 300, B
4, 500, A
];
Main:
LOAD * INLINE [
ID, Amount2
1, 100
2, 230
3, 300
4, 500
];
I am creating a Pivot table and the purpose of this table is to show
Amount from Amount1 Column if there is Type B associated with the particular ID else take the amount from Main.
Dimension:
ID
Type
Expression:
sum({<ID={2,3,4}>}if(Match(Type,'B'),Amount1,Amount2))
The output looks like this. I do not need the Type column. It is there to just to show where the amount is coming from.
My data should like this
ID | Amount |
---|---|
2 | 140 |
3 | 300 |
4 | 300 |
this works for this sample data set and i am trying to see if i can incorporate this logic in the main data set.
IF(sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1,Amount2)))=0,sum(Amount2),sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1,Amount2))))
if you want only for [id ]2,3,4 please use ID={2,3,4}
regards
Pradosh
Hi,
try with a if condition : if(sum({<ID={2,3,4},Type={"B"}>}Amount1)=0,sum({<ID={2,3,4}>}Amount2),sum({<ID={2,3,4},Type={"B"}>}Amount1))
I just responded to it.
Can you please explain the If condition?
Here is another one you can try
If(SubStringCount(Concat(DISTINCT '|' & Type & '|'), '|B|') = 1, Sum({<Type = {'B'}, ID = {2,3,4}>}Amount1), Sum({<ID = {2,3,4}>}Amount2))
I used a simpler version of it
IF(sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1)))=0,
sum(Amount2),
sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1))))
One issue:
When i do partial sum then the sum does not include the amount for ID 3.
Why is that?