Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

johann_bauer
New Contributor II

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
MVP
MVP

Re: Convert datetime serial number

Try

Load

Date(Num#(MonthName)) as Date,

Description,

Amount

Resident TempBudget ;

7 Replies
MVP
MVP

Re: Convert datetime serial number

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;

MVP
MVP

Re: Convert datetime serial number

Try

Load

Date(Num#(MonthName)) as Date,

Description,

Amount

Resident TempBudget ;

MVP
MVP

Re: Convert datetime serial number

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
New Contributor II

Re: Convert datetime serial number

Thank you guys for the fast respond,

looks like that adding the Num# did the trick.

Thanks

MVP
MVP

Re: Convert datetime serial number

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

johann_bauer
New Contributor II

Re: Convert datetime serial number

Hi Sunny,

your Solution also works perfectly.

MVP
MVP

Re: Convert datetime serial number

Thanks for getting back