Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Tags (3)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: How to select the first date

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

8 Replies
mov
Esteemed Contributor III

Re: How to select the first date

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

Re: How to select the first date

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

Re: How to select the first date

Hi Michael,

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

Not applicable

Re: How to select the first date

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

mov
Esteemed Contributor III

Re: How to select the first date

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

Re: How to select the first date

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

mov
Esteemed Contributor III

Re: How to select the first date

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

Re: How to select the first date

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

Community Browser