Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ...
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;
can you pleae share sample data set
Not sure I understand... would you be able to share a sample?
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
];
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
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..
Highlighted row is the expected result, I just added it , to see the expected result... they are not part of the data, sorry ...
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;
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;
That works ... Seem so simple once you give the anwer ... Thanks