Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
fyrworx25
Contributor III
Contributor III

Creating a flag in a load statement within a subset

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:

   

IDStatusDateFlag
111AAA2017/01/011
222TTT2017/01/020
333AAA2017/01/031
111BBB2017/01/040
222AAA2017/01/051
333CCC2017/01/060

Thank you.

9 Replies
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?

fyrworx25
Contributor III
Contributor III
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
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

Great dreamer's dreams never fulfilled, they are always transcended.
fyrworx25
Contributor III
Contributor III
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
fyrworx25
Contributor III
Contributor III
Author

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:

 

111AAA2017/01/01
333AAA2017/01/03
222DDD2017/01/05

Thank you for your responses so far.

PrashantSangle

Hi,

What is your required output???

Regards,

Prashant

Great dreamer's dreams never fulfilled, they are always transcended.
fyrworx25
Contributor III
Contributor III
Author

If I could get this I would be happy:

Table1:

IDStatusDateFlag
111AAA2017/01/011
333AAA2017/01/031
222DDD2017/01/051