Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mikaschm61
New Contributor

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

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.

RowNoMATNRTYPEDATE
192951012017-08-08
292951032017-08-11
392951012017-08-15
492951012017-08-21
592951012017-09-01
692951032017-09-09
792951032017-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:

RowNoMATNRTYPEDATEDATE_RECEIPT
192951012017-08-082017-08-08
292951032017-08-112017-08-08
392951012017-08-152017-08-15
492951012017-08-212017-08-21
592951032017-09-092017-08-21
692951032017-09-112017-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
1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

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

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

4 Replies
antoniotiman
Honored Contributor III

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

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

mikaschm61
New Contributor

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

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
Honored Contributor III

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

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

mikaschm61
New Contributor

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

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,

Community Browser