Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner
Partner

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 ;

View solution in original post

7 Replies

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 ;

View solution in original post

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,

Partner
Partner

Re: Convert datetime serial number

Thank you guys for the fast respond,

looks like that adding the Num# did the trick.

Thanks

Re: Convert datetime serial number

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

Partner
Partner

Re: Convert datetime serial number

Hi Sunny,

your Solution also works perfectly.

Re: Convert datetime serial number

Thanks for getting back