Num(Date)

[#1]

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.

[jerem1234]

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.

[#4]

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]

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

[#6 · Accepted solution]

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.

[#7]

Thanks Massimo.

Your expression working too.:)

[MarcoWedel]

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

[#9]

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