Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Hi Michael,
Thanks for the answer but I need the minimum date. And min doesn't work on a date field.
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
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
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
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
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