topic New sum in load script dilema in QlikView

New sum in load script dilema

Thu, 04 Aug 2011 12:21:59 GMT

Hi, im still having a headach with using 'sum' in the load script. I am trying to generare the table below: -

Ref	no	shouldbe
1	1	14
1	4	14
1	5	14
2	1	4
2	1	4
2	1	4
1	1	14
1	1	14
2	1	4
3	1	3
3	1	3
3	1	3
1	1	14
1	1	14

I am trying to do the sum of 'no' grouped by Ref to produce and additional column that matches 'shouldbe'.

The code I am using is: -

Main:
LOAD Ref,
[no],
shouldbe
FROM
data.xlsx
(ooxml, embedded labels, table is Sheet1);

Main2:
LOAD Ref as ref2,
[no],
sum([no]) as sum,
shouldbe

RESIDENT Main
GROUP BY Ref , [no], shouldbe ;
DROP TABLE Main;

However the table that is being produced looks like this :-

ref2	no	sum	shouldbe
3	1	3	3
1	4	4	14
2	1	4	4
1	1	5	14
1	5	5	14

I only want the load to produce one 'ref 1' with the sum being 14.

I'm baffled, any help would be greatly received.

Many thanks

New sum in load script dilema

lironbaram — Thu, 04 Aug 2011 12:32:47 GMT

hei your Script should look like this

Main:
LOAD Ref,
[no],
shouldbe
FROM
data.xlsx
(ooxml, embedded labels, table is Sheet1);

left join(Main)

LOAD Ref,
sum([no]) as sum
RESIDENT Main
GROUP BY Ref;

Re: New sum in load script dilema

SunilChauhan — Thu, 04 Aug 2011 12:49:58 GMT

try it out

New sum in load script dilema

kaushiknsolanki — Thu, 04 Aug 2011 12:57:29 GMT

Hi,

Try this.

Data:
LOAD * INLINE [
Ref, no, shouldbe
1, 1, 14
1, 4, 14
1, 5, 14
2, 1, 4
2, 1, 4
2, 1, 4
1, 1, 14
1, 1, 14
2, 1, 4
3, 1, 3
3, 1, 3
3, 1, 3
1, 1, 14
1, 1, 14
];

Data1:
Load Ref as REF,
no as NO,
Sum(no) as Count,
max(shouldbe) as max
Resident Data group by Ref,no;

Regards,

Kaushik Solanki

New sum in load script dilema

Thu, 04 Aug 2011 13:00:42 GMT

Liron,

Works perfectly thankyou.