Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
johann_bauer
Partner - Contributor III
Partner - Contributor III

Convert datetime serial number

Hi community,

this is my first post so try to be gentle .

I am new to qlik Sense and doesnt have any experience with qlik view so far.

My problem is that I cant convert a datetime serial number into a date again:

I am importing an excel file with a date format like 01.01.2106, it looks like qlik sense automaticly cast this format into his own

datetime serial format. After the load i transform the table into crosstable my table looks like this:

MonthNameDescriptionAmount
42370Some Random Text10
42401
Some Random Text
12

Now I tried to load the resident table with some a converted date.

My script looks like this:

Load

Date(MonthName) as Date, // also tried Date(MonthName,'DD/MM/YYYY') as Date

Description,

Amount

Resident TempBudget ;

But im only getting a empty column.

I hope there is an easy way to do this.

KR

Johann

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

Load

Date(Num#(MonthName)) as Date,

Description,

Amount

Resident TempBudget ;

View solution in original post

7 Replies
sunny_talwar

Try a resident load after your CrossTable LOAD.

FinalTable:

NoConcatenate

LOAD Date(Num#(MonthName, '##'), 'DD/MM/YYYY') as Date,

          Description,

          Amount,

Resident TempBudget;

DROP Table TempBudget;

swuehl
MVP
MVP

Try

Load

Date(Num#(MonthName)) as Date,

Description,

Amount

Resident TempBudget ;

sunny_talwar

Also, in your post you mentioned datetime? Does your date contain time infor also? If it does, then try this:

Date(Floor(Num#(MonthName, '##')), 'DD/MM/YYYY') as Date,

johann_bauer
Partner - Contributor III
Partner - Contributor III
Author

Thank you guys for the fast respond,

looks like that adding the Num# did the trick.

Thanks

sunny_talwar

Just out of curiosity, my suggestion did not work Johann?

johann_bauer
Partner - Contributor III
Partner - Contributor III
Author

Hi Sunny,

your Solution also works perfectly.

sunny_talwar

Thanks for getting back