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.

[sunny_talwar]

Try this

If(Sum({<ID = {"=Only(TOTAL <ID> {<Type = {'B'}>} Type) = 'B'"}*{2,3,4}, Type = {'B'}>}Amount1) > 0,

Sum({<ID = {"=Only(TOTAL <ID> {<Type = {'B'}>} Type) = 'B'"}*{2,3,4}, Type = {'B'}>}Amount1),

Sum({<ID = {2,3,4}, Type -= {'B'}>}Amount2))

With ID as dimension

[Anil_Babu_Samineni]

PFA

[pradosh_thakur]

Hi

as i thought 1 also qualify and should have value 130 i have added that too. If you don;t you can exclude that.

PFA

regards

Pradosh

[jduluc12]

Can You please paste the expression here?

[jduluc12]

Can you please paste the expression here?

[Anil_Babu_Samineni]

Sunny provide expression as well, I am not changing anything from UI. I just use this?

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

] Where Type = 'B';

Main:

LOAD * INLINE [

    ID, Amount2

    1, 100

    2, 230

    3, 300

    4, 500

];

[jduluc12]

i can not change the load script. I have to do it in Expression.

[sunny_talwar]

I gave you an expression? No response to what I gave?

[Anil_Babu_Samineni]

Then look for Talwar expression? seems working..

If(Sum({<ID = {"=Only(TOTAL <ID> {<Type = {'B'}>} Type) = 'B'"}*{2,3,4}, Type = {'B'}>}Amount1) > 0,

Sum({<ID = {"=Only(TOTAL <ID> {<Type = {'B'}>} Type) = 'B'"}*{2,3,4}, Type = {'B'}>}Amount1),

Sum({<ID = {2,3,4}, Type -= {'B'}>}Amount2))