topic Re: Count or Sum in Load Script in QlikView

Count or Sum in Load Script

dmac1971 — Wed, 06 Aug 2014 12:41:19 GMT

Here's an example of my data :

ITEM	DESCRIPTION	ORD QTY	RESERVED QTY	ON HAND	LINE STATUS
10.12.45	Flag	1	0	8	BACKORD
10.12.55	Pole	6	6	16	RELEASED
10.12.632	Rope	10	10	16	RELEASED
10.12.654	Grass Seed	5	5	73	RELEASED
10.12.45	Flag	1	0	0	RELEASED
10.12.55	Pole	1	1	15	RELEASED
10.12.200	Deck Chair	2	1	1	BACKORD
10.12.45	Flag	2	0	0	RELEASED

I can load everything in no problem, however want to sum and count during load, is this possible? For example can I count the number of open orders for Flags, and the total qty items ordered and reserved? This is so I can easily flag up via a list box any item that doesn't have enough stock?

Re: Count or Sum in Load Script

avinashelite — Wed, 06 Aug 2014 12:45:12 GMT

Hi Dermot,

Yes its possible, but you need to use GROUP BY in the script.

eg:

load *,

count(ITEM)

sql select *

from group by filed1,filed2;

Re: Count or Sum in Load Script

joshabbott — Wed, 06 Aug 2014 12:45:57 GMT

Are you using SQL in your load script? If so, it should be as easy as a SQL statement that returns what you want. If you are loading from excel, you may need to use aggregates in a preceding load. Could you include your load script for this table?

Re: Count or Sum in Load Script

VishalWaghole — Wed, 06 Aug 2014 12:53:06 GMT

Try this,

T1:

LOAD * Inline [

ITEM, DESCRIPTION, ORDQTY, RESERVEDQTY, ON HAND, LINE STATUS

10.12.45, Flag, 1, 0 8, BACKORD

10.12.55, Pole, 6, 6 16, RELEASED

10.12.632, Rope, 10, 10 16, RELEASED

10.12.654, Grass Seed, 5, 5, 73, RELEASED

10.12.45, Flag, 1, 0, 0, RELEASED

10.12.55, Pole, 1, 1, 15, RELEASED

10.12.200, Deck Chair, 2, 1, 1, BACKORD

10.12.45, Flag, 2, 0, 0, RELEASED

];

T2:

LOAD Count(ITEM) as OPENORDERS,

Sum(ORDQTY) as TOTALORDERS,

sum(RESERVEDQTY) as TOTALRESERVEORD

Resident T1

where Match(DESCRIPTION,'Flag');

Hope you solve this problem.

-- Regards,

Vishal Waghole

Re: Re: Count or Sum in Load Script

dmac1971 — Wed, 06 Aug 2014 13:20:02 GMT

Have got this working, however its rolling everything up together, ie showing 5 orders, and 28 items, which is the total no items and the total being sold. See QV File attached. The expressions give me what I want to see, however I'd like then to be able to have a list box showing Yes or No for stock fulfilled. I thought if I could do the counts in the load script the listbox would be easy then. Alternatively can you have an if statement in the list box with Yes/No as the available options and then use this to filter?

Re: Count or Sum in Load Script

dmac1971 — Thu, 07 Aug 2014 09:58:36 GMT

Anyone give me an in on this one? TIA!

Re: Count or Sum in Load Script

kaushiknsolanki — Thu, 07 Aug 2014 10:00:37 GMT

Hi,

You can create aggregated table in script with some predefined dimensions and then you can link that with actual data model.

Use group by functions

Regards,

Kaushik Solanki

Re: Count or Sum in Load Script

VishalWaghole — Fri, 08 Aug 2014 04:30:17 GMT

No need to use group by function, cause in load statement all columns are inside aggregate function.

If we uses columns with aggregate function in Load statement then its mandatory to use group by function.

Hope you understand logic.

-- Regards,

Vishal Waghole

Re: Count or Sum in Load Script

dmac1971 — Fri, 08 Aug 2014 07:50:20 GMT

Nope I haven't been able to crack this one. Sometimes I find getting stuck in Qlikview very hard to find a solution!!

Re: Count or Sum in Load Script

VishalWaghole — Fri, 08 Aug 2014 07:58:17 GMT

Tell me whats problem are you facing now...

Will try to resolve it.

-- Regards,

Vishal Waghole