Sum in another column

[psk180590]

Hello All,

I have my sales data(sample) as follows

Code         Product      Year     Sales

N1               AA             2016     10

N1               BB             2016     50

N1               AA             2017     77

N2               CA             2016     80

N2               CD             2017     98

N2               EE             2015     120    

N3               YY             2016     567

N3               XX              2016     895

N4               RR             2016     986    

N4               CC             2017     132

What i would like to have is a sum of sales against each code for all the Products in that code

Code         Product      Year     Sales   Market

N1               AA             2016     10        137

N1               BB             2016     50        137

N1               AA             2017     77         137

N2               CA             2016     80          298

N2               CD             2017     98          298

N2               EE             2015     120        298

N3               YY             2016     567         1462

N3               XX              2016     895         1462

N4               RR             2016     986         1118

N4               CC             2017     132          1118

I want to save this table in an qvd.

TIA!!

[sunny_talwar · Accepted solution]

May be like this

Table:

LOAD * Inline [

Code,  Product, Year,     Sales

N1, AA, 2016,     10

N1, BB, 2016,     50

N1, AA, 2017,     77

N2, CA, 2016,     80

N2, CD, 2017,     98

N2, EE, 2015,     120   

N3, YY, 2016,     567

N3, XX, 2016,     895

N4, RR, 2016,     986   

N4, CC, 2017,     132

];

Left Join (Table)

LOAD Code,

  Sum(Sales) as Market

Resident Table

Group By Code;

STORE Table into Table.qvd (qvd);

DROP Table Table;

[Kushal_Chawda]

Map:

mapping LOAD Code,

                        sum(Sales) as Market

FROM Source

Group by Code:

Data:

LOAD *,

         applymap('Map',Code,0) as Market

FROM Source;

STORE Data into Data.qvd;

DROP Table Data;