Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Previous Document Number

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

ItemDocument Number
ARCP005
ARCP017
BRCP012
BRCP026

B

B

RCP032

RCP045

CRCP018
CRCP024
CRCP033
Tags (1)
1 Solution

Accepted Solutions

Re: Previous Document Number

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;

9 Replies

Re: Previous Document Number

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)

Not applicable

Re: Previous Document Number

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

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.

Not applicable

Re: Previous Document Number

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

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.

Not applicable

Re: Previous Document Number

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


Not applicable

Re: Previous Document Number

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

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;

Not applicable

Re: Previous Document Number

Hi Ruben

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

kind regards

Nayan