Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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;
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;
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;