topic Re: Previous Value in QlikView

Previous Value

Tue, 06 Jun 2017 15:02:19 GMT

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.

Re: Previous Value

el_aprendiz111 — Tue, 06 Jun 2017 15:30:11 GMT

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);

Re: Previous Value

Tue, 06 Jun 2017 15:37:45 GMT

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?

Re: Previous Value

el_aprendiz111 — Tue, 06 Jun 2017 16:16:50 GMT

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;

Re: Previous Value

Sergey_Shuklin — Tue, 06 Jun 2017 19:15:56 GMT

Hello, Michele!

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

PFA.

Re: Previous Value

Sergey_Shuklin — Wed, 07 Jun 2017 07:10:50 GMT

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