Min date by repeated status

[eugeniyaromanov]

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?

[tresesco]

Do you need this in UI or script?

[eugeniyaromanov]

Script

[Vengatesh]

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;

[tresesco · Accepted solution]

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

[eugeniyaromanov]

Hi!

thanks, I've tried it. 

Sorry, but your script left some other dates (

[eugeniyaromanov]

It worked!

thanks!