Pivot table problem

[jduluc12]

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

1. ID 2 should show amount 140 becasue it is associated with Type B in the Table Aux.
2. ID 3 should show amount 300 becasue there is no Type B's amount in Table Aux so it should take amount 300 from from Table Main's Amount2 column.
3. ID 4 should show amount 300 becasue it is associated with Type B in the Table Aux.

[jduluc12]

this works for this sample data set and i am trying to see if i can incorporate this logic in the main data set.

[pradosh_thakur · Accepted solution]

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

[Anonymous]

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

[jduluc12]

I just responded to it.

[jduluc12]

Can you please explain the If condition?

[sunny_talwar]

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

[jduluc12]

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

[jduluc12]

One issue:

When i do partial sum then the sum does not include the amount for ID 3.

Why is that?