topic Creating a flag in a load statement within a subset in QlikView

Creating a flag in a load statement within a subset

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Creating a flag in a load statement within a subset

Colin-Albert — Mon, 06 Mar 2017 14:00:29 GMT

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?

Re: Creating a flag in a load statement within a subset

Anonymous — Mon, 06 Mar 2017 14:01:20 GMT

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"

Re: Creating a flag in a load statement within a subset

PrashantSangle — Mon, 06 Mar 2017 14:04:26 GMT

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

Re: Creating a flag in a load statement within a subset

PrashantSangle — Mon, 06 Mar 2017 14:05:47 GMT

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

Re: Creating a flag in a load statement within a subset

Anonymous — Mon, 06 Mar 2017 14:11:11 GMT

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.

Re: Creating a flag in a load statement within a subset

PrashantSangle — Mon, 06 Mar 2017 14:17:04 GMT

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

Re: Creating a flag in a load statement within a subset

Anonymous — Mon, 06 Mar 2017 14:30:46 GMT

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.

Re: Creating a flag in a load statement within a subset

PrashantSangle — Mon, 06 Mar 2017 14:37:38 GMT

Hi,

What is your required output???

Regards,

Prashant

Re: Creating a flag in a load statement within a subset

Anonymous — Mon, 06 Mar 2017 14:41:37 GMT

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