topic Re: SUM in chart table in QlikView

SUM in chart table

Anonymous — Tue, 26 Sep 2017 13:38:45 GMT

I have a table with many products, and I need to sum some field with same PCODE, where I will add *0 in thr PRD column , D1,D2,D3,D4 are sum, and have the other field the same as *1

Here is a sample table:

I tried this, but not getting very far

Bridge_Table:

load

//SubField(PRD,'*',1)&'*0' as PRD

PCODE,

Sum(D1) as D1,

Sum(D2) as D2,

Sum(D3) as D3,

Sum(D4) as D4

Resident TEST_PRD

group by PCODE;

That does not work well ... I do have the sum in a $syn table, but can't make this line to work //SubField(PRD,'*',1)&'*0' as PRD

And after that, I have no clue how to make the other field equal to the *1 of each product.

To help me explain, here is the result I would like:

Hope someone can give me hints on how to acheive this ...

Re: SUM in chart table

Anil_Babu_Samineni — Tue, 26 Sep 2017 13:41:40 GMT

can you pleae share sample data set

Re: SUM in chart table

sunny_talwar — Tue, 26 Sep 2017 13:43:48 GMT

Not sure I understand... would you be able to share a sample?

Re: SUM in chart table

Anonymous — Tue, 26 Sep 2017 13:48:54 GMT

TEST_PRD:

LOAD * INLINE [

PRD,PCODE,D1,D2,D3,D4,Type,Vers

123*1,123,2,3,4,4,AAA,C2

123*2,123,4,7,4,3,AAA,C2

123*4,123,5,3,4,2,AAA,C2

123*0,123,11,13,12,9,AAA,C2

134*3,134,4,3,9,1,BBB,C1

134*2,134,4,3,6,9,BBB,C1

134*5,134,4,3,3,8,BBB,C1

134*0,134,12,9,18,18,BBB,C1

234*1,234,4,7,8,7,AAA,C3

234*2,234,4,7,81,6,AAA,C3

234*3,234,4,74,8,5,AAA,C3

234*4,234,44,7,8,4,AAA,C3

234*0,234,56,95,105,22,AAA,C3

];

Re: SUM in chart table

Anonymous — Tue, 26 Sep 2017 13:51:49 GMT

product 123*1,123*2, etc represent product in different location ... I need the sum of the D1 to D4 field for all the ones that have the same thing before the * ... the PCODE field in fact ... but to be able to see the new location I created 123*0, when I filter by other field , like Type, I need the new row to have also the good value in these other field ...

I know I might not be explaining right ... sorry about that

Re: SUM in chart table

Anil_Babu_Samineni — Tue, 26 Sep 2017 13:56:25 GMT

Do we need to take highlight rows as well?? If so, What is the output result? If without those three then i may understand your intend..

Re: SUM in chart table

Anonymous — Tue, 26 Sep 2017 13:58:56 GMT

Highlighted row is the expected result, I just added it , to see the expected result... they are not part of the data, sorry ...

Re: SUM in chart table

marcus_malinow — Tue, 26 Sep 2017 14:12:48 GMT

Maybe something like this:

TEST_PRD:

LOAD * INLINE [

PRD,PCODE,D1,D2,D3,D4,Type,Vers

123*1,123,2,3,4,4,AAA,C2

123*2,123,4,7,4,3,AAA,C2

123*4,123,5,3,4,2,AAA,C2

134*3,134,4,3,9,1,BBB,C1

134*2,134,4,3,6,9,BBB,C1

134*5,134,4,3,3,8,BBB,C1

234*1,234,4,7,8,7,AAA,C3

234*2,234,4,7,81,6,AAA,C3

234*3,234,4,74,8,5,AAA,C3

234*4,234,44,7,8,4,AAA,C3

];

CONCATENATE (TEST_PRD)

LOAD

PCODE & '*0' as PRD,

PCODE,

sum(D1) as D1,

sum(D2) as D2,

sum(D3) as D3,

sum(D4) as D4,

Type,

Vers

RESIDENT TEST_PRD

GROUP BY PCODE, Type, Vers;

Re: SUM in chart table

Anil_Babu_Samineni — Tue, 26 Sep 2017 14:17:31 GMT

May be try this?

Table:

Load * From Table;

Load Pcode & '0' as PRD, Pcode, Sum(D1) as D1, .....

Resident Table Group By Pcode, Type, Vers;

Re: SUM in chart table

Anonymous — Tue, 26 Sep 2017 14:27:55 GMT

That works ... Seem so simple once you give the anwer ... Thanks