Previous Value

[#1]

Hi Qlikers,

i need to know how can i obtain this result:

I have a table with ITEM, MONTHS, MOV DATE, INVENTORY DATE and MIN MOV DATE.

There are several Items that have some movement dates; i've taken the maximum movement date per month.

PROBLEM:

I need to calculate a date that is the INVENTORY DATE if minimum movement date is higher than the inventory date.

It's the MIN MOV DATE if the minimum movement date is equal to the mov date and

it's the last minimum MOV DATE when min mov date is lower then the mov date.

I need to solve it on the script.

Maybe everything is more clear in the picture.

Thanks in advance.

[el_aprendiz111]

Hi,

from script:

LOAD *, IF([MIN MOV DATE]<=[MOV DATE],[MOV DATE],[INVENTORY DATE]) AS CALCU;
LOAD ITEM,
MONTHS,
[MOV DATE],
[INVENTORY DATE],
[MIN MOV DATE]
FROM
[..\..\..\Documents\Downloads\B.xlsx]
(ooxml, embedded labels, table is Hoja1);

[#3]

Thank you Fer for your answer,

but the MIN MOV DATE is a field with only one date for item.

MIN MOV DATE is the lowest mov date about the item and it's everytime lower or equal than the MOV DATE.

I will have only MOV DATE with this condition (IF([MIN MOV DATE]<=[MOV DATE],[MOV DATE]).

Any suggestions?

[el_aprendiz111]

Hi Michelle

This can work for you

tmp:
LOAD ITEM,
MONTHS,
[MOV DATE],
[INVENTORY DATE]

FROM
[..\..\..\Documents\Downloads\B.xlsx]
(ooxml, embedded labels, table is Hoja1);

minDt: LOAD min([MOV DATE]) AS MinDate  Resident tmp;

LET v_MinDare  = date(floor(peek('MinDate')),'DD/MM/YYYY');

SUMARY:LOAD *, '$(v_MinDare)' AS [MIN MOV DATE], IF('$(v_MinDare)'<=[MOV DATE],[MOV DATE],[INVENTORY DATE]) AS CALCU Resident tmp;



DROP Table tmp,minDt;

[Sergey_Shuklin]

Hello, Michele!

In attached example you may find a front-end result. I'll try to solve the back-end task tomorrow!

PFA.

[Sergey_Shuklin · Accepted solution]

And here is the back-end solution (PFA).

For filling missing dates/values often can be usefull this script condition:

if(isnull(Field1),peek(Field1),Field1) as Field1

It takes previous value from Field1 if Field1 is null, and if end of the line is "as Field1" (e.g. the same field) so the condition will be cycled, otherwise it will take previous value just one time.

You also can find informative this topic: Generating Missing Data In QlikView