topic Re: Using TOTAL Function in Script in QlikView

Using TOTAL Function in Script

zach_paz — Fri, 22 Jan 2016 18:48:43 GMT

Hi All,

I need to sum up a field by two other fields. See example below.

SUM(TOTAL <cip,geoid> [2014 Employment]) as CIPbyGeoidWage

The issue is that the script does not like the second field in the TOTAL function. <cip,geoid>

Is there a way to sum based on aggregating by two fields in the load script? From what i can tell, the formula works if I only have one field like, <cip>, but i need the data aggregated by both (<cip, geoid>).

Thanks in advance!

Zach

Re: Using TOTAL Function in Script

Oleg_Troyansky — Fri, 22 Jan 2016 18:53:09 GMT

Hi Zach,

I'm surprised that only the second attribute is being rejected. In my mind, TOTAL should not work at all in the script, because it's a Chart feature.

In the script, we use GROUP BY load for aggregating data:

LOAD

cip,

geoid,

SUM( [2014 Employment]) as CIPbyGeoidWage

RESIDENT

MyTable

GROUP BY

cip,

geoid

;

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Re: Using TOTAL Function in Script

sunny_talwar — Fri, 22 Jan 2016 18:53:51 GMT

I was not aware that TOTAL keyword is allowed in script at all.

I would usually do this like this:

Left Join (Table)

LOAD cip,

geoid

Sum([2014 Employment]) as CIPbyGeoidWage

Resident Table

Group By cip, geoid;

Re: Using TOTAL Function in Script

swuehl — Fri, 22 Jan 2016 19:12:26 GMT

Is there a way to sum based on aggregating by two fields in the load script? From what i can tell, the formula works if I only have one field like, <cip>, but i need the data aggregated by both (<cip, geoid>).

Zach,

could you post a sample QVW or script that demonstrates a Sum(TOTAL FIELD) or Sum(TOTAL<cip> FIELD)?

As already pointed out by others I don't think TOTAL qualifier is allowed in the script (don't get confused by the syntax checker without running the script).

If you made it work, I would be interested to see your sample.

Stefan