Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

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

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 

1 Solution

Accepted Solutions
Oleg_Troyansky

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(LastDateas LastServDate
Resident TempServDates

cheers,

Oleg Troyansky

View solution in original post

5 Replies
Oleg_Troyansky

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(LastDateas LastServDate
Resident TempServDates

cheers,

Oleg Troyansky

swuehl
MVP
MVP

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]
;

sfatoux72
Partner
Partner

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

swuehl
MVP
MVP

tmumaw
Specialist II
Specialist II
Author

Thanks Oleg.  It worked perfectly.