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.
Solved! Go to Solution.
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!
my date values are showing as below.
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
a shorter solution might be the usage of the alt() function, like jerem1234 suggested:
Alt(Num(Date#(MyDate,'YYYYMMDD')),MyDate) as MyDateNum
Instead of converting all dates to numbers, you could also convert all numbers to dates (for better readability) like:
Date(Alt(Num(Date#(MyDate2,'YYYYMMDD')),MyDate2)) as MyDateNum2
hope this helps
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.
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: