topic Re: Pivot table problem in QlikView

Pivot table problem

jduluc12 — Mon, 16 Oct 2017 11:36:45 GMT

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:

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

ID 2 should show amount 140 becasue it is associated with Type B in the Table Aux.
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.
ID 4 should show amount 300 becasue it is associated with Type B in the Table Aux.

Re: Pivot table problem

sunny_talwar — Mon, 16 Oct 2017 11:53:47 GMT

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

Re: Pivot table problem

Anil_Babu_Samineni — Mon, 16 Oct 2017 11:53:54 GMT

PFA

Re: Pivot table problem

pradosh_thakur — Mon, 16 Oct 2017 12:00:25 GMT

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

Re: Pivot table problem

jduluc12 — Mon, 16 Oct 2017 12:03:56 GMT

Can You please paste the expression here?

Re: Pivot table problem

jduluc12 — Mon, 16 Oct 2017 12:04:21 GMT

Can you please paste the expression here?

Re: Pivot table problem

Anil_Babu_Samineni — Mon, 16 Oct 2017 12:06:20 GMT

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

];

Re: Pivot table problem

jduluc12 — Mon, 16 Oct 2017 12:08:32 GMT

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

Re: Pivot table problem

sunny_talwar — Mon, 16 Oct 2017 12:09:51 GMT

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

Re: Pivot table problem

Anil_Babu_Samineni — Mon, 16 Oct 2017 12:10:18 GMT

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

Re: Pivot table problem

jduluc12 — Mon, 16 Oct 2017 12:11:25 GMT

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

Re: Pivot table problem

pradosh_thakur — Mon, 16 Oct 2017 12:14:12 GMT

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

Re: Pivot table problem

Anonymous — Mon, 16 Oct 2017 12:14:44 GMT

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

Re: Pivot table problem

jduluc12 — Mon, 16 Oct 2017 12:14:49 GMT

I just responded to it.

Re: Pivot table problem

jduluc12 — Mon, 16 Oct 2017 12:18:15 GMT

Can you please explain the If condition?

Re: Pivot table problem

sunny_talwar — Mon, 16 Oct 2017 12:18:29 GMT

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

Re: Pivot table problem

jduluc12 — Mon, 16 Oct 2017 12:41:36 GMT

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

Re: Pivot table problem

jduluc12 — Tue, 17 Oct 2017 11:55:24 GMT

One issue:

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

Why is that?