Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Num(Date)


Hi,

I need the date field values as num.

Some of the records are coming as numbers but some are coming as date format.

I'm using the below command to make them all alike.

If(E_TIMESTAMP='YYYYMMDD',NUM(Floor(Date(E_TIMESTAMP))),E_TIMESTAMP) as E_TIMESTAMP

But that don't help. i used LEFT(E_TIMESTAMP),8) too.but that's not sorting my prob either.

Any suggestions plz?

Thanks in advance.

1 Solution

Accepted Solutions
Not applicable
Author

Finally got it.:)

Here is the command to get this done.

If(LEN(E_TIMESTAMP)>5,NUM(Date#(E_TIMESTAMP,'YYYYMMDD'),'#####')
,E_TIMESTAMP) as E_TIMESTAMP

Thanks all for the reply.


View solution in original post

9 Replies
jerem1234
Specialist II
Specialist II

Can U give some examples of what your dates looks like? You can also try date#() function with the alt functions to accommodate multiple date formats like:

alt(date#(FIELD, 'YYYYMMDD'), date#(FIELD, 'YYYY-MM-DD'), date#(FIELD, 'MM/DD/YYYY'))

Hope this helps!

Colin-Albert

Get the Dates Right

This link should help you understand the date functions.

Not applicable
Author

my date values are showing as below.

41847

41850

20141128

20141129 etc.

That means it's both in num and date format as well. I want to have them all in num format like 41847,41850 etc..

I already used Date# though, but don't see any difference. I have to use If() here

Thanks.

maxgro
MVP
MVP

if

41847

41850

20141128

20141129

are your values (field)

if(len(field)>5, num(MakeDate(left(field,4), mid(field,5,2), Right(field,2))), field) as newfield

Not applicable
Author

Finally got it.:)

Here is the command to get this done.

If(LEN(E_TIMESTAMP)>5,NUM(Date#(E_TIMESTAMP,'YYYYMMDD'),'#####')
,E_TIMESTAMP) as E_TIMESTAMP

Thanks all for the reply.


Not applicable
Author

Thanks Massimo.

Your expression working too.:)

MarcoWedel

Hi,

a shorter solution might be the usage of the alt() function, like jerem1234 suggested:

QlikCommunity_Thread_148716_Pic1.JPG

tabDates1:

LOAD *,

    Alt(Num(Date#(MyDate,'YYYYMMDD')),MyDate) as MyDateNum

Inline [

MyDate

41847

41850

20141128

20141129

];

Instead of converting all dates to numbers, you could also convert all numbers to dates (for better readability) like:

QlikCommunity_Thread_148716_Pic2.JPG

tabDates2:

LOAD *,

     Date(Alt(Num(Date#(MyDate2,'YYYYMMDD')),MyDate2)) as MyDateNum2

Inline [

MyDate2

41847

41850

20141128

20141129

];

hope this helps

regards

Marco

Not applicable
Author

Hey Marco,

Thanks for the suggestion.

Actually we have another transformation layer where we are converting NUM into date fields. So to keep it uniform, we have to convert into NUM instead of Date.

Thanks.

MarcoWedel

Actually you have textual representations combined with underlying numerical values if you convert to QlikView Date, Time or Timestamp. Your intermediate transformation layer should therefore work with both numerical and date input.

Maybe these links can help you:

QlikView Date fields

http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

regards

Marco