topic Re: Convert Numeric Date to Actual Date (YYYY-MM-DD) in QlikView

Convert Numeric Date to Actual Date (YYYY-MM-DD)

tmumaw — Fri, 12 Feb 2016 20:26:18 GMT

Hi All,

Need a little help. I need to convert a numeric date to the actual date. Here is my script. I need to find the first date and last date of services. What am I miss?

Thanks

TempServDates:
Load
[Ship To] as ShipTo,
Min([Service_Date]) as FirstDate,
Max([Service_Date]) as LastDate
Resident VBRP_R
Group by [Ship To]
;

ServDates:
Load *,
Date(Date#(FirstDate,'YYYY-MM-DD')) as FirstServDate,
Date(Date#(LastDate,'YYYY-MM-DD')) as LastServDate
Resident TempServDates

Re: Convert Numeric Date to Actual Date (YYYY-MM-DD)

Oleg_Troyansky — Fri, 12 Feb 2016 20:43:57 GMT

Hi Thom,

assuming that Service_Date is a proper Date field (not string, not an 8-digit number like YYYYMMDD, etc...) - then min() and max() should return the numeric representation of the dates. All you need to do it to format it back into the date (there is no need to cast strings to dates using Date#):

TempServDates:
Load
[Ship To] as ShipTo,
Min([Service_Date]) as FirstDate,
Max([Service_Date]) as LastDate
Resident VBRP_R
Group by [Ship To]
;

ServDates:
Load *,
Date(FirstDate) as FirstServDate,
Date(LastDate) as LastServDate
Resident TempServDates

cheers,

Oleg Troyansky

Re: Convert Numeric Date to Actual Date (YYYY-MM-DD)

swuehl — Fri, 12 Feb 2016 23:28:16 GMT

Try

ServDates:

Load *,
Date(FirstDate,'YYYY-MM-DD')) as FirstServDate,
Date(LastDate,'YYYY-MM-DD') as LastServDate;

Load
[Ship To] as ShipTo,
Min([Service_Date]) as FirstDate,
Max([Service_Date]) as LastDate
Resident VBRP_R
Group by [Ship To]
;

or if you don't need to keep the numbers in separate fields (the dates will still keep the numeric representation):

ServDates:

Load
Date(Min([Service_Date]),'YYYY-MM-DD')) as FirstServDate,
Date(Max([Service_Date]),'YYYY-MM-DD') as LastServDate,

[Ship To] as ShipTo
Resident VBRP_R
Group by [Ship To]
;

Re: Convert Numeric Date to Actual Date (YYYY-MM-DD)

sfatoux72 — Sat, 13 Feb 2016 00:18:50 GMT

‌

Hi,

Reading your script, I understand that the initial format of your date is 'YYYY-MM-DD´.

MIn and Max works only with numbers

Try this:

TempServDates:
Load
[Ship To] as ShipTo,
MinString([Service_Date]) as FirstDate,
MaxString([Service_Date]) as LastDate
Resident VBRP_R
Group by [Ship To]
;

ServDates:
Load *,
Date(Date#(FirstDate,'YYYY-MM-DD')) as FirstServDate,
Date(Date#(LastDate,'YYYY-MM-DD')) as LastServDate
Resident TempServDates

Re: Convert Numeric Date to Actual Date (YYYY-MM-DD)

swuehl — Sat, 13 Feb 2016 01:20:38 GMT

Maybe also have a look at

Get the Dates Right

Why don’t my dates work?

Re: Convert Numeric Date to Actual Date (YYYY-MM-DD)

tmumaw — Tue, 16 Feb 2016 14:33:05 GMT

Thanks Oleg. It worked perfectly.