Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
mbreton-dubo
Contributor III
Contributor III

SUM in chart table

Hi

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:

PRD-TEST1.png

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:


PRD-TEST2.png

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

1 Solution

Accepted Solutions
marcus_malinow
Specialist III
Specialist III

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;

View solution in original post

9 Replies
Anil_Babu_Samineni

can you pleae share sample data set

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

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

mbreton-dubo
Contributor III
Contributor III
Author

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

];

mbreton-dubo
Contributor III
Contributor III
Author

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

Anil_Babu_Samineni

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

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mbreton-dubo
Contributor III
Contributor III
Author

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

marcus_malinow
Specialist III
Specialist III

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;

View solution in original post

Anil_Babu_Samineni

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mbreton-dubo
Contributor III
Contributor III
Author

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