Count or Sum in Load Script

[dmac1971]

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?

[avinashelite]

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;

[joshabbott]

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?

[VishalWaghole]

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

[dmac1971]

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?

[dmac1971]

Anyone give me an in on this one?  TIA!

[kaushiknsolanki]

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

[VishalWaghole]

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

[dmac1971]

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

[VishalWaghole]

Tell me whats problem are you facing now...

Will try to resolve it.

-- Regards,

Vishal Waghole