topic Sum in another column in QlikView

Sum in another column

psk180590 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sum in another column

sunny_talwar — Tue, 25 Apr 2017 18:09:45 GMT

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;

Re: Sum in another column

Kushal_Chawda — Tue, 25 Apr 2017 18:36:58 GMT

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;