Using TOTAL Function in Script

[zach_paz]

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

[Oleg_Troyansky]

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!

[sunny_talwar · Accepted solution]

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;

[swuehl]

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