topic Previous Document Number in QlikView

Previous Document Number

Tue, 17 Mar 2015 12:28:09 GMT

We process receipt transactions and i want to display the previous document number. so for eg in the table below,

for Item A, I want to display RCP005 ,

for Item B, I want to display RCP032,

for Item C, I want to display RCP024

How do i do this?

Thank you

regards

Nayan

Item	Document Number
A	RCP005
A	RCP017

B	RCP012
B	RCP026
B B	RCP032 RCP045

C	RCP018
C	RCP024
C	RCP033

Re: Previous Document Number

rubenmarin — Tue, 17 Mar 2015 12:39:09 GMT

Hi Nayan, if you add a Row number to document number, and they are sorted by Item and Document number, ie for this data:

Data:

LOAD *, RowNo() as RowNumber;

LOAD * Inline [

Item,Document Number

A,RCP005

A,RCP017

B,RCP012

B,RCP026

B,RCP032

B,RCP045

C,RCP018

C,RCP024

C,RCP033

];

You can use this expression:

FirstSortedValue([Document Number], -RowNumber, 2)

Re: Previous Document Number

Tue, 17 Mar 2015 12:50:40 GMT

Hi Ruben

Thank you for your prompt reply. Will try it out. Not sure if it will work. Let me just elaborate on my query. When we receive stock, it must show the current documnet number and unit cost. Also on the same line, i want to display what our previous receipt cost and document was.

Let me know if you understand . if not , i can upload a mock qlikview model.

kind regards

Nayan

Re: Previous Document Number

rubenmarin — Tue, 17 Mar 2015 13:01:05 GMT

Ok, you can use Previous() or Peek() functions. Again you have to start with a table where your data is sorted by type and document number (or by date of that document number)

Data:

LOAD *, If(Previous(Item)=Item, Previous([Document Number])) as PrevDocument, RowNo() as RowNumber;

LOAD * Inline [

Item,Document Number

A,RCP005

A,RCP017

B,RCP012

B,RCP026

B,RCP032

B,RCP045

C,RCP018

C,RCP024

C,RCP033

];

So if this reads an Item equal to the previous Item, it gets the previous document number and saves in PrevDocument field.

Re: Previous Document Number

Tue, 17 Mar 2015 13:33:36 GMT

Hi Ruben

Thank you for your reply. Unfortuantely , In my actual database , is not sorted by type and document number. Will have a try at your formula and will let you know.

regards

Nayan

Re: Previous Document Number

rubenmarin — Tue, 17 Mar 2015 13:43:58 GMT

Hi Nayan, if you have some kind of date or another field to give you wich order should be for [Document Number] or if this [Document Number] is the current order you can do something like:

data: // Your current load

LOAD...

Select....

PrevDocuments:

LOAD Item,

[Document number],

If(Previous(Item)=Item, Previous([Document Number])) as PrevDocument

Resident data order by Item, [Document number];

Left Join (data) LOAD * Resident PrevDocuments;

DROP Table PrevDocuments;

If it's only one record for each Item and document number it should work.

Re: Previous Document Number

Tue, 17 Mar 2015 14:21:30 GMT

Hi Ruben

Thank you . Will try it out. Much appreciated. By the way, this is the first time im aware of the "Previous function" .

kind regards

Nayan

Re: Previous Document Number

Wed, 18 Mar 2015 08:03:04 GMT

Hi Ruben

Your scripting worked fine. Thank you.

Sorry for the delayed reply, I left for home.

Just one more question, in my actual Qlikivew model, I have unit cost per receipt per item. How do i link this unit cost to the "previous document".

so for eg, for item C, for each receipt below there is a unit cost

RCP018 - $20.00

RCP024 - $22.00

RCP033 - $25.00

So the result i would like to achieve is RCP024 with unit cost of $22.00.

kind regards

Nayan

Re: Previous Document Number

rubenmarin — Wed, 18 Mar 2015 08:13:07 GMT

Hi Nayan, it should be:

data: // Your current load

LOAD...

Select....

PrevDocuments:

LOAD Item,

[Document number],

If(Previous(Item)=Item, Previous([Document Number])) as PrevDocument,

If(Previous(Item)=Item, Previous([Unit Cost])) as PrevUnitCost,

Resident data order by Item, [Document number];

Left Join (data) LOAD * Resident PrevDocuments;

DROP Table PrevDocuments;

Re: Previous Document Number

Wed, 18 Mar 2015 08:35:47 GMT

Hi Ruben

It worked. Thank you so much for your help. Much appreciated.

kind regards

Nayan