Creating a flag in a load statement within a subset

[Anonymous]

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.

[Colin-Albert]

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?

[Anonymous]

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"

[PrashantSangle]

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

[PrashantSangle]

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

[Anonymous]

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.

[PrashantSangle]

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

[Anonymous]

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.

[PrashantSangle]

Hi,

What is your required output???

Regards,

Prashant

[Anonymous]

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