topic Num(Date) in QlikView

Num(Date)

Fri, 16 Jan 2015 19:21:22 GMT

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.

Re: Num(Date)

jerem1234 — Fri, 16 Jan 2015 19:26:36 GMT

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)

Colin-Albert — Fri, 16 Jan 2015 19:28:13 GMT

Get the Dates Right

This link should help you understand the date functions.

Re: Num(Date)

Fri, 16 Jan 2015 21:40:26 GMT

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.

Re: Num(Date)

maxgro — Fri, 16 Jan 2015 21:53:00 GMT

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

Re: Num(Date)

Fri, 16 Jan 2015 22:03:13 GMT

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.

Re: Num(Date)

Fri, 16 Jan 2015 22:06:30 GMT

Thanks Massimo.

Your expression working too.:)

Re: Num(Date)

MarcoWedel — Fri, 16 Jan 2015 23:26:27 GMT

Hi,

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

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:

tabDates2:
LOAD *,
     Date(Alt(Num(Date#(MyDate2,'YYYYMMDD')),MyDate2)) as MyDateNum2
Inline [
MyDate2
41847
41850
20141128
20141129
];

hope this helps

regards

Marco

Re: Num(Date)

Mon, 19 Jan 2015 11:24:18 GMT

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)

MarcoWedel — Mon, 19 Jan 2015 19:53:27 GMT

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