Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikaschm61
Contributor II
Contributor II

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
Master III
Master III

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

View solution in original post

4 Replies
antoniotiman
Master III
Master III

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
Contributor II
Contributor II
Author

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
Master III
Master III

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
Contributor II
Contributor II
Author

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,