Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Num(Date)

Finally got it.Smiley Happy

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.


9 Replies
jerem1234
Valued Contributor II

Re: Num(Date)

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!

Re: Num(Date)

Get the Dates Right

This link should help you understand the date functions.

Not applicable

Re: Num(Date)

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.

MVP
MVP

Re: Num(Date)

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

Re: Num(Date)

Finally got it.Smiley Happy

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

Re: Num(Date)

Thanks Massimo.

Your expression working too.Smiley Happy

Re: Num(Date)

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

Re: Num(Date)

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.

Re: Num(Date)

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

Community Browser