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

[mazacini]

Hi

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

[maxgro]

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;

[mazacini]

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

[maxgro]

[mazacini]

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

[maxgro · Accepted solution]

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)

More on load

LOAD data into QlikView

Directory;

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)

// order by order by Branch_GPClass, date  --> error

;

final:

NoConcatenate

load

  Branch,

    GPClass,

    Branch_GPClass,

    GPAmt as OriginalGPAmt,    // original data

  GPStat as OriginalGPStat,      // original data

    DateBranch,

  if(Previous(Branch_GPClass)=Branch_GPClass, Previous(GPAmt), 0) as GPAmt,

  'Open Stk' as GPStat

Resident source

order by Branch_GPClass, date;

DROP Table source;

[mazacini]

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

[maxgro]

yes,

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