topic Re: Create Open Stock Next Week from Close Stock This Week (Peek or Previous?) in QlikView

Create Open Stock Next Week from Close Stock This Week (Peek or Previous?)

mazacini — Fri, 26 Jun 2015 20:57:51 GMT

I have loaded a table into Qlikview which has details of CLOSING stock per "GPClass" Per Branch at week end (Date is contained as number in DateBranch field).

I want to create records showing these values as OPENING stock in the following week:

My intention was to sort the records by Branch, GPClass and DateBranch.

Then, for each record, I would lookup the previous GPClass (using Branch_GPClass), and if it is the same as the current Branch_GPClass, then I would create a "OpenStock" record for that DateBranch entry, USING THE GPAmt VALUE FOR THE PREVIOUS ENTRY.

If it is not the same, I create the same record, but with GPAmt as 0.

Can anyone help me write the script.

I want to append the resulting table to the existing field.

Spreadsheet attached might provide better understanding.

Thanks

Joe

Re: Create Open Stock Next Week from Close Stock This Week (Peek or Previous?)

maxgro — Fri, 26 Jun 2015 22:21:43 GMT

source:

LOAD Branch,

GPClass,

Branch_GPClass,

GPAmt,

GPStat,

DateBranch,

date(subfield(DateBranch, '.', 1)) as date

FROM

[Create Opening Stock records from Closing Stock.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

cross:

load distinct date Resident source;

join (cross) load distinct Branch_GPClass Resident source;

left join (cross) load * Resident source;

DROP Table source;

final:

NoConcatenate

load *,

if(Peek(Branch_GPClass)=Branch_GPClass, Peek(GPAmt), 0) as NewGPAmt,

'Open Stk' as NewGPStat

Resident cross

order by Branch_GPClass, date;

DROP Table cross;

Re: Create Open Stock Next Week from Close Stock This Week (Peek or Previous?)

mazacini — Fri, 26 Jun 2015 22:49:49 GMT

Thank you Massimo

The solution you offer correctly calculates the new Opening Stock figure.

However, I need the new data to be in the same format as the existing data.

In other words, a new record is created for each line, with new value showing your closing stock calculation in GPAmt, and with the value "Close Stk" in GPStat

Attached spreadsheet illustrates.

Thanks

Joe

Re: Create Open Stock Next Week from Close Stock This Week (Peek or Previous?)

maxgro — Fri, 26 Jun 2015 23:05:01 GMT

Re: Create Open Stock Next Week from Close Stock This Week (Peek or Previous?)

mazacini — Sat, 27 Jun 2015 13:18:00 GMT

Hi Massimo

That worked!

One clarification - I wanted to retain the "Close Stk" data. To achieve this, I just reloaded the original data at the end of the script. You might be able to suggest a better way.

I would like to understand the reason for the creation of the "cross" table. I suspect it is something to do with arranging the data to allow for the operation of the Previous statement?

I think I am confused as to the nature of a Resident Load - is it the case that the data loads in reverse?

If you get time to respond, that would be great.

If not, thank you for your help so far.

I will mark your answer as correct.

Joe

Re: Create Open Stock Next Week from Close Stock This Week (Peek or Previous?)

maxgro — Sat, 27 Jun 2015 13:52:40 GMT

The below script is easier, without join (no cross table)

In bold 2 of the original fields.

Resident load: is a load from a previously loaded table; after you load from the excel you can do a resident load from the source table.

Here I used it because I need an ordered load ( by Branch_GPClass, date) and in QlikView you can only do an ordered load using a resident load (if you add an order in the load from excel you get an error, bold)

Re: Create Open Stock Next Week from Close Stock This Week (Peek or Previous?)

mazacini — Sat, 27 Jun 2015 13:57:17 GMT

I think I understand.

So you need to load the data in such a way that the Previous statement can work as required.

Many thanlks

Joe

Re: Create Open Stock Next Week from Close Stock This Week (Peek or Previous?)

maxgro — Sat, 27 Jun 2015 14:08:06 GMT

yes,

ordered by Branch_GPClass and date, so you can get (with previous or peek) the amount of the previous record