topic Re: Sum of rows based on a group in load script in QlikView

Sum of rows based on a group in load script

newqlik2017 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

Below is my data set. I'm looking to group the account by state based on groups in the load script.

Also, how can I include the County, City, Zip columns in the final result set. Attached is a qvw for reference.

Data set:

STATE	COUNTY	CITY	ZIP	ACCOUNT	GROUP	AMOUNT
CA	NAPA	XXX	1001	A123	Low	100
CA	NAPA	YYY	1002	A123	Low	200
CA	MONO	AAA	1003	A123	High	300
CA	ORANGE	BBB	1004	A123	Medium	400
NY	SUFFOLK	AAA	2001	A123	Low	50
NY	SUFFOLK	BBB	2002	A123	Low	70
NY	SUFFOLK	BBB	2002	A123	High	90
PA	BUTLER	CCC	3001	A123	Low	110
PA	COLUMBIA	DDD	3002	A123	Low	120
PA	CLINTON	AAA	3003	A123	Low	130
PA	CLINTON	AAA	3003	A123	High	140
PA	CLINTON	BBB	3004	A123	Medium	150

Expected Result (also need to include County, City, Zip somehow as a part of final data set so that users can filter based on the state, county, city and zip):

STATE	ACCOUNT	GROUP	AMOUNT
CA	A123	LOW	300
CA	A123	MEDIUM	400
CA	A123	HIGH	300
NY	A123	LOW	120
NY	A123	HIGH	90
PA	A123	LOW	360
PA	A123	MEDIUM	150
PA	A123	HIGH	140

Re: Sum of rows based on a group in load script

Anil_Babu_Samineni — Wed, 18 Apr 2018 14:18:20 GMT

Can you add this at end, And check..

Final:

NoConcatenate

LOAD STATE, ACCOUNT, GROUP, Sum(AMOUNT) as AMOUNT Resident Table Group By STATE, ACCOUNT, GROUP Order By GROUP;

DROP Table Table;

Re: Sum of rows based on a group in load script

jaumecf23 — Wed, 18 Apr 2018 14:18:49 GMT

I have attached an example. Check if this is usefull for you.

Re: Sum of rows based on a group in load script

newqlik2017 — Wed, 18 Apr 2018 14:25:21 GMT

Jaume, did you attach a different test.qvw file?

Re: Sum of rows based on a group in load script

newqlik2017 — Wed, 18 Apr 2018 14:27:02 GMT

Thanks Anil. That works.

Now, how do I bring in other columns County, City and Zip in the UI. Any ideas?

Re: Sum of rows based on a group in load script

jaumecf23 — Wed, 18 Apr 2018 14:30:09 GMT

Yes, my mistake. I have update the older message with the correct file.

Re: Sum of rows based on a group in load script

Anil_Babu_Samineni — Wed, 18 Apr 2018 14:31:39 GMT

Simply adding this?

Right Join(Table)

LOAD STATE, ACCOUNT, GROUP, Sum(AMOUNT) as AMOUNT Resident Table Group By STATE, ACCOUNT, GROUP Order By GROUP;

Re: Sum of rows based on a group in load script

Anil_Babu_Samineni — Fri, 20 Apr 2018 00:16:44 GMT

I suggest you to close this thread by flag "Correct Answer"