topic Re: Re: Calculation of non-zero items in QlikView

Calculation of non-zero items

Tue, 30 Sep 2014 12:05:04 GMT

I have fact-table which contains purchase and sales of my items and list of items in separate table. Let say I totally I have 10 items in my list. I want to calculate rest of items on the particular day and how many items I have on my store LOAD * INLINE [ No, Qty, Store Item1, 5, St1 Item1, -5, St1 Item2, 4, St1 Item2, -2, St1 Item3, 3, St1 ]; Items: LOAD * INLINE [ No Item1 Item2 Item3 Item4 ]; I want to have In result table : Store No of Items ST1 2 That means that I have only Item2 and Item3 to the end of month on my Store. Item1 have been sold. There are no Item. It looks that I should use aggr-function, but I can not right correct expression. Than you in advance

Re: Calculation of non-zero items

anbu1984 — Tue, 30 Sep 2014 12:31:50 GMT

Sales:

LOAD * INLINE [

No, Qty, Store

Item1, 5, St1

Item1, -5, St1

Item2, 4, St1

Item2, -2, St1

Item3, 3, St1 ];

Items:

LOAD * INLINE [

Item1

Item2

Item3

Item4 ];

NoConcatenate

Final:

Load Store as Str,Count(No) Where Qty > 0 Group by Store;

Load Store,No,Sum(Qty) As Qty Resident Sales Group by Store,No;

Re: Calculation of non-zero items

Tue, 30 Sep 2014 12:35:54 GMT

Anbu, thank you for your reply, but I want to change my data model, because I need calculate these data (and some other for selected period). My idea is calculate it the final application in pivot table (or other chats)/

Re: Re: Calculation of non-zero items

anbu1984 — Tue, 30 Sep 2014 12:40:48 GMT

Check this

Re: Calculation of non-zero items

Tue, 30 Sep 2014 13:56:24 GMT

Thanks, it works