topic Re: How to select the first date in QlikView

How to select the first date

Tue, 16 Apr 2013 14:47:18 GMT

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

Re: How to select the first date

Anonymous — Tue, 16 Apr 2013 17:05:46 GMT

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

Re: How to select the first date

Tue, 16 Apr 2013 19:29:06 GMT

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

Re: How to select the first date

Wed, 17 Apr 2013 07:42:56 GMT

Hi Michael,

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

Re: How to select the first date

Wed, 17 Apr 2013 07:45:02 GMT

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

Re: How to select the first date

Anonymous — Thu, 18 Apr 2013 00:29:42 GMT

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

Re: How to select the first date

Thu, 18 Apr 2013 07:09:02 GMT

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

Re: How to select the first date

Anonymous — Thu, 18 Apr 2013 14:20:42 GMT

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

Re: How to select the first date

Thu, 18 Apr 2013 14:59:04 GMT

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