Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Load Script - Sum against reference

Hi,

I'm looking for advice on what I feel should have a really easy solution (but for some reason I cant get it to work).

I have a table similar to the one below: -

Refno
11
11
11
21
21
21
11
11
21
31
31
31
11
11

What I am trying to do is sum 'No' against the 'Ref' within the load script to produce: -

Refnotot
117
117
117
214
214
214
117
117
214
313
313
313
117
117

I have tried if(PREVIOUS(ref)=ref, PEEK([no]),0) + [no] as test, however this does not give the result I am after.

Can anyone advise on how I can achieve this within the load?

Many thanks

Tags (3)
3 Replies
Not applicable

Load Script - Sum against reference

Think I found the solution using count and grouping.

Main:

LOAD Ref,

     [no],

     tot

FROM

data.xlsx

(ooxml, embedded labels, table is Sheet1);

Main2:

LOAD Ref as ref2,

     [no],

     count([no]) as cnt,

     tot

RESIDENT Main

GROUP BY Ref , [no], tot;

DROP TABLE Main;

MVP
MVP

Re: Load Script - Sum against reference

You still want all of the rows, but with the total per Ref on each row?

LEFT JOIN (MyTable)
LOAD
Ref
,count(no) as tot
RESIDENT MyTable
;

That said, I generally recommend against doing totals in the script, as they won't be sensitive to selections.  Of course, if you WANT your totals to not be sensitive to selections, the script is a good place to do that.  But otherwise, this would normally be handled in a chart.  Something like this:

dimension 1 = Ref
dimension 2 = no
expression  = count(total <Ref> no)

Not applicable

Load Script - Sum against reference

Many thanks for that answer. 

The reason I am trying to do this in the load script is because I need to include the data within a pivot table against the reference; the pivot being on date and containing other information.  Using a count/sum calculation within the dimension just seemed to break the table, I think doing it this way will give a cleaner result (or at least one that will work).

Community Browser