Previous Document Number

[#1]

Hi

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

[rubenmarin]

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)

[#3]

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

[rubenmarin]

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.

[#5]

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

[rubenmarin]

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.

[#7]

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

[#8]

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

[rubenmarin · Accepted solution]

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;

[#10]

Hi Ruben

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

kind regards

Nayan