Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need to create a flag. The flag must be the earliest date, where the status is AAA.
I am not sure how to get this to work in a load statement, to look like this:
ID | Status | Date | Flag |
111 | AAA | 2017/01/01 | 1 |
222 | TTT | 2017/01/02 | 0 |
333 | AAA | 2017/01/03 | 1 |
111 | BBB | 2017/01/04 | 0 |
222 | AAA | 2017/01/05 | 1 |
333 | CCC | 2017/01/06 | 0 |
Thank you.
Your example flags each row where the status is AAA regardless of date.
The earliest date with the status AAA would just flag the first row.
Is this correct?
Or do you want the earliest date for each ID where the status is AAA?
Can you clarify with better example data?
The earliest for each status. Apologies for that.
Exactly this: "Or do you want the earliest date for each ID where the status is AAA"
Hi,
try like below
min_date:
Load min(Date) as min_date from table1;
temp:
Load ID,'1' as flag from table1
where exists(Date,min_date);
table1:
Load * from table1;
left join
Load ID,flag from temp;
drop table temp;
Regards,
Prashant
Hi,
try like below
min_date:
Load min(Date) as min_date from table1;
temp:
Load Status,'1' as flag from table1
where exists(Date,min_date);
table1:
Load * from table1;
left join
Load Status,flag from temp;
drop table temp;
Regards,
Prashant
Thank you for the replies.
However I need the ID's first date with status AAA.
Taking the min date will return ID 222 date 2017/01/02
This status is not AAA.
Hi,
Try 2nd solution
Algorithm
1: find min date
2: on the basis of min date I am taking only status.
3: then using status, I am Left join flag field to main table.
Regards,
Prashant
Thanks for your reply again.
I think it should be the other way,
1. Find status
2. Find min(date) of status
3. Create flag.
I have attached a more complex example. The status needs to be either AAA or DDD. And I need to flag the very first time the ID fell into one of these statuses.
I need to create the flag in the load. If we look at the list going down. The flagged IDs/Dates and Statuses should be:
111 | AAA | 2017/01/01 |
333 | AAA | 2017/01/03 |
222 | DDD | 2017/01/05 |
Thank you for your responses so far.
Hi,
What is your required output???
Regards,
Prashant
If I could get this I would be happy:
Table1:
ID | Status | Date | Flag |
111 | AAA | 2017/01/01 | 1 |
333 | AAA | 2017/01/03 | 1 |
222 | DDD | 2017/01/05 | 1 |