Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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'));
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;
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'));
Hi!
thanks, I've tried it.
Sorry, but your script left some other dates (
It worked!
thanks!