Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to select the first date

Hi guys,

I have a set of data that consists of the following fields:

ID_Number;

OrderDate; and

Flag.

The data represents something that looks like this:

ID_Number                       OrderDate                          Flag

8304125002087               03/06/2011                         0

8304125002087               07/07/2012                         1

8304125002087               03/06/2013                         1

8212285002087               03/07/2011                         0

8212285002087               17/06/2012                         0

8212285002087               04/05/2013                         1

7601285002087               06/06/2011                         1

7601285002087               06/08/2012                         0

What I need to achieve out of this is to be able to identify (in the script) the first OrderDate where Flag = 1 and Populate a new field with this date.

For instance in this example I need the following populated:

ID_Number                      OrderDate                           Flag                    NewDate

8304125002087               03/06/2011                         0                         07/07/2012

8304125002087               07/07/2012                         1                         07/07/2012

8304125002087               03/06/2013                         1                         07/07/2012

8212285002087               03/07/2011                         0                         04/05/2013

8212285002087               17/06/2012                         0                         04/05/2013

8212285002087               04/05/2013                         1                         04/05/2013

7601285002087               06/06/2011                         1                         06/06/2011

7601285002087               06/08/2012                         0                         06/06/2011

How would I go about doing this?

Your assistance will be highly appreciated.

Kindly thanking you,

Marius

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Marius,

The "date" fields in load inline are in fact have "text" format, not "date", hence min() and max() functions are not applicable.  See attahce, I converted them into the date format.  And made a few more changes to demo the result.

Regards,
Michael

View solution in original post

8 Replies
Anonymous
Not applicable
Author

Marius,

That will be, if your table is called "data":

LEFT JOIN (data) LOAD DISTINCT
ID_Number,
max(OrderDate) as NewDate
RESIDENT data
WHERE Flag=1
GROUP BY ID_Number;

Regards,
Michael

Not applicable
Author

Hi,

You can do the following as well:

LOAD ID_Number,

OrderDate,

Flag,

case when Flag = 1

     then DATE

else OrderDate End as NewDate,

From TABLE;

Rebeca

Not applicable
Author

Hi Michael,

Thanks for the answer but I need the minimum date. And min doesn't work on a date field.

Not applicable
Author

Hi Rebeca,


Thanks for the answer but I need the minimum date where the Flag = 1.

Currently your solution will give the date for that specific line item if the flag = 1. But I need the minimum date of the customer date where flag = 1.

Please let me know if I need to explain a bit furher.

Thanks,
Marius

Anonymous
Not applicable
Author

Sorry was not careful, sure it must be min() for the earliest date.  And, if your OrderDate is in date format, min() works as good as max().  If OrderDate is a text field, you can make it a date:

date(date#(OrderDate, 'DD/MM/YYYY'), 'DD/MM/YYYY') as OrderDate

Regards,

Michael

Not applicable
Author

Thanks Michael for you feedback.

I must be dumb as I can't seem to get this to work. 🙂

I have attached a test.qvw.

Would you be so kind as to help me out with this please?

Thanks,
Marius

Anonymous
Not applicable
Author

Marius,

The "date" fields in load inline are in fact have "text" format, not "date", hence min() and max() functions are not applicable.  See attahce, I converted them into the date format.  And made a few more changes to demo the result.

Regards,
Michael

Not applicable
Author

Thanks Michael.

I just added a WHERE Flag = 1 to the RESIDENT load and everything works perfectly now.


Thanks for your help.

Really appreciate it.


Regards,
Marius