Calculate date, based on Min(Date) - Load Script

[Anonymous]

Hello Qlik Friends,

I have a little question. Hopefully someone have any idea or a solution for that.

I tried it with Previous() function, but it doenst worked, because I got only the previous date of Column("DATE"). I would like to create a column which contains the DATE, based on TYPE.

I searched for a similiar discussion, but I found only this discussion.

https://community.qlik.com/message/1188605#1188605

This is the table with the columns RowNo, MATNR, TYPE and DATE, which is sorted by DATE and RowNo.

RowNo	MATNR	TYPE	DATE
1	9295	101	2017-08-08
2	9295	103	2017-08-11
3	9295	101	2017-08-15
4	9295	101	2017-08-21
5	9295	101	2017-09-01
6	9295	103	2017-09-09
7	9295	103	2017-09-11

I would like to create a column, which contains only the DATE for TYPE 101, but with the last DATE.

See table below:

RowNo	MATNR	TYPE	DATE	DATE_RECEIPT
1	9295	101	2017-08-08	2017-08-08
2	9295	103	2017-08-11	2017-08-08
3	9295	101	2017-08-15	2017-08-15
4	9295	101	2017-08-21	2017-08-21
5	9295	103	2017-09-09	2017-08-21
6	9295	103	2017-09-11	2017-08-21

My First script:

/*******************************************************

Table:

Load * inline

[

RowNo,MATNR,TYPE,DATE

1,9295,101,2017-08-08

2,9295,103,2017-08-11

3,9295,101,2017-08-15

4,9295,101,2017-08-21

5,9295,101,2017-09-09

6,9295,103,2017-09-11

];

stalwar1, maybe you have an idea.

Thanks in advance.

Best regards,

mikaschm61

[antoniotiman]

Hi Mikael,

may be this

LOAD RowNo,
MATNR,
TYPE,
DATE,
If(TYPE <> '101',Peek(DATE),DATE) as DATE_RECEIPT
FROM
https://community.qlik.com/message/1348341
(html, codepage is 1252, embedded labels, table is @1);

Regards,

Antonio

[Anonymous]

Hi antoniotiman,

thank you for your answer. As you see in the RowNo=7, the date is 2017-09-09, but it should be 2017-09-01.

I tried Peek() and Previous() functions, but didn't find any way to do it.

Hopefully, you have an idea.

Regards,

Mikael

[antoniotiman · Accepted solution]

Try this

LOAD RowNo,
MATNR,
TYPE,
DATE,
If(TYPE <> '101',Peek(DATE_RECEIPT),DATE) as DATE_RECEIPT
FROM
https://community.qlik.com/message/1348341
(html, codepage is 1252, embedded labels, table is @1);

[Anonymous]

Hello antoniotiman‌,

thank you very much for your help.

I didn't know, that the Peek() function can do it.

It worked very well.

Best regards,