topic Inventory units for each week in QlikView

Inventory units for each week

Thu, 11 Mar 2010 15:51:03 GMT

Dear all

I have a table with 30 million transactions( in/out from stock).

It is easy to calculate a rolling balance (takes 2-3 minutes), but what i need, is the inventorylevel week by week (end of week) until today.

Until now, I have tried to insert the dates for the week end using while and intervalmatch. But with 2,350,000 various combinations, there will be quite many rows.

My final thought was, maybe to do something with set analysis, but still no success.

Do any of you have any great ideas on , how to achieve the above requirement? I have attached an example of what the data looks like

Thank you in advance for any help.

/Martin

Inventory units for each week

Thu, 11 Mar 2010 18:30:12 GMT

You can probably use the WeekEnd and WeekStart statements for this. Will be easier on your system to add those to each record than to use IntervalMatch.

Inventory units for each week

Oleg_Troyansky — Thu, 11 Mar 2010 22:36:58 GMT

Mark,

calculating the balances from the transactions is quite a chore... Here is the general idea:

1. Generate distinct lists of all Items and all Weeks in your range

2. Join between the two, to generate all possible permutations of Items and Weeks

3. Left Join into this table summary of transactions, aggregated by Item and Week.

4. Sorting the table by Item and Transaction Date, reload the table, calculating the running total :

if(previous(Item) = Item, peek(RunningTotal) + Transaction, Transaction) as RunningTotal

Assuming that you have ALL the transactions from each Item (from the beginning of the Item's life cycle), you should be able to calculate all weekly totals for all Items.

Inventory units for each week

Fri, 12 Mar 2010 05:35:05 GMT

Hi Oleg, thank you for your reply,

As i wrote in my post, its is easy to create the balance of the items (se my attach file in previous post).

What tricks me is the amount of data (30 mio rows, with 2,3 mio items). If you add that up it is quite many rows. What i basically need, is some thoughts on how to add the weeks without any transactions, in the best way.

I hav tried to do a while (time is an issue here), Intervalmatch (performance is an issue on this one).

/Martin

Inventory units for each week

Fri, 12 Mar 2010 06:54:13 GMT

Hi Martin ,

"------- some thoughts on how to add the weeks without any transactions, in the best way . ------"

Since your data size is huge, I think addition of Master Calender will work for you .

You can copy Master calender (refered code from some other links) from link : http://community.qlik.com/forums/t/26678.aspx & modified to accomodate your dates .

Then you can link your Transaction Dates with Dates in Master Calender & pull report based on Weeks mentioned in Master calender .

I think that will solve your query .

Let me know in case of any dobuts.

Cheers ,

Bhushan N

Inventory units for each week

Fri, 12 Mar 2010 08:42:07 GMT

Hi Bhusnan

It will not solve my issue. That script will just add a calendar.

Let take an example..the last transaction on a specific item is january 5, but we still have 10 units on stock, then i wont get the actual value as of today, when adding a calendar.

/Martin

Inventory units for each week

Fri, 12 Mar 2010 09:57:42 GMT

Hi Martin ,

Check this link : http://community.qlik.com/forums/t/26601.aspx

Cheers,

Bhushan N

Inventory units for each week

Mon, 23 Aug 2010 09:09:54 GMT

Nice technique with the application Martin, thansk for your help.