topic Min date by repeated status in QlikView

Min date by repeated status

eugeniyaromanov — Fri, 03 May 2019 06:42:46 GMT

Hello!

I have a problem with peeking min date with repeating status.

I have an ID, a date when status is changed and a status.

Any status can be assigned to the ID as many times as people need.

I need to get first date on each status.

Problem is: If status is repeated, I need to get first date again.

Data table:

ID	Date	Status
1111	01.01.2019	Red
1111	02.02.2019	Blue
1111	03.03.2019	Blue
2222	01.01.2019	Red
2222	02.02.2019	Red
1111	04.04.2019	Red
3333	01.01.2019	Red
3333	02.02.2019	Blue
2222	03.03.2019	Blue
4444	04.04.2019	Blue

Needed table:

ID	Min Date	Status
1111	01.01.2019	Red
1111	02.02.2019	Blue
1111	04.04.2019	Red
2222	01.01.2019	Red
2222	03.03.2019	Blue
3333	01.01.2019	Red
3333	02.02.2019	Blue
4444	04.04.2019	Blue

As you can see, ID 1111 gets status Red two times and I need both date.

And somehow I can't do that 😞

Any ideas?

Re: Min date by repeated status

tresB — Fri, 03 May 2019 06:55:56 GMT

Do you need this in UI or script?

Re: Min date by repeated status

eugeniyaromanov — Fri, 03 May 2019 07:00:20 GMT

Script

Re: Min date by repeated status

Vengatesh — Fri, 03 May 2019 07:01:45 GMT

Try this in the script.

Table1:
LOAD ID,
Date,
Status
FROM
Source;
NoConcatenate
Table:
LOAD *
Resident Table1
Where ID&'-'&Status <> Previous(ID&'-'&Status)
Order by ID,Date;
DROP Table Table1;

Re: Min date by repeated status

tresB — Fri, 03 May 2019 07:09:27 GMT

Try like:

Load * Inline [
ID,Date,Status
1111,01.01.2019,Red
1111,02.02.2019,Blue
1111,03.03.2019,Blue
2222,01.01.2019,Red
2222,02.02.2019,Red
1111,04.04.2019,Red
3333,01.01.2019,Red
3333,02.02.2019,Blue
2222,03.03.2019,Blue
4444,04.04.2019,Blue
] where not(ID= Peek('ID') and Status = Peek('Status'));

Re: Min date by repeated status

eugeniyaromanov — Fri, 03 May 2019 07:46:44 GMT

Hi!

thanks, I've tried it.

Sorry, but your script left some other dates (

Re: Min date by repeated status

eugeniyaromanov — Fri, 03 May 2019 07:47:41 GMT

It worked!

thanks!