SUM in chart table

[Anonymous]

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:

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

[Anil_Babu_Samineni]

can you pleae share sample data set

[sunny_talwar]

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

[Anonymous]

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

];

[Anonymous]

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

[Anonymous]

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 · Accepted solution]

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;

[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;

[Anonymous]

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