Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

New sum in load script dilema

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

Refnoshouldbe
1114
1414
1514
214
214
214
1114
1114
214
313
313
313
1114
1114

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

ref2nosumshouldbe
3133
14414
2144
11514
15514

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

Tags (3)
1 Solution

Accepted Solutions
Highlighted
lironbaram
Honored Contributor II

New sum in load script dilema

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;

4 Replies
Highlighted
lironbaram
Honored Contributor II

New sum in load script dilema

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;

chauhans85
Esteemed Contributor

Re: New sum in load script dilema

try it out

New sum in load script dilema

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

Not applicable

New sum in load script dilema

Liron,

Works perfectly thankyou.

Community Browser