Pivot count coming wrong

[#1]

Hi,

If I have two tables like bwlo, can you please tell me what is wrong with my pivot table.

Table_A:

Load * Inline

[ColA,ColB,ColD

A,2001,C

A,1001,D

A,1001,E

B,1001,EE

C,2001,DD

];

Table_B:

Load * Inline

[ColA,ColB, ColE,ColF

D,1001,ccc,C

D,1001,ddd,aD

D,3001,eee,aE

D,1001,fff,aaEE

E,2001,ggg,ggDD

];

Now when I tried to do pivot table after loading above tables I don't get correct count.

I have used ColA and ColB as parameter and Expression is = Count(ColA).

But pivot table is showing wrong count. For ColA as D, and Column B as 1001, Pivot table shows only count as '1' instead of '3'.

For ColA as A, and Column B as 1001, Pivot table shows only count as '1' instead of '2'.

When I load only Table_A count in pivots are fine.

But When I try to load both tables, pivot is giving incorrect info.

Any advice will be really appreciated.

[kaushiknsolanki · Accepted solution]

Hi,

   This script will generate the synthetic table. Try below code to get correct output.

Table_A:

Load * Inline

[ColA,ColB,ColD

A,2001,C

A,1001,D

A,1001,E

B,1001,EE

C,2001,DD

];

Concatenate

Load * Inline

[ColA,ColB, ColE,ColF

D,1001,ccc,C

D,1001,ddd,aD

D,3001,eee,aE

D,1001,fff,aaEE

E,2001,ggg,ggDD

];

  I have also attached the sample application.

  Hope this will Help.

Regards,

Kaushik Solanki

[#3]

Thanks for Concateate option. Biut is it possible without concatenate.

[kaushiknsolanki]

HI,

   Without concatenate it will create a synthetic table which will sure give u wrong result.

Regards,

Kaushik Solanki

[#5]

Thanks for the response this was helpful.