Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Can't format date in Load Script

Hi guys,

I don't understand what is causing this problem. I have a date field which I extract from a SQL server using cast(date as date) function, which returns an ISO format YYYY-MM-DD.

In the Master Calendar autogeneration script, i'm trying to format the date like:

date(date#([Date], 'YYYY-MM-DD'),'DD/MM/YYYY') as Date.

I have a basic fact table, and a master calendar table for this particular date field, however, it is displaying in YYYY-MM-DD format. I've tried the above formula on the field in the fact table and in the master calendar, and it still displays incorrectly.

What's going on?

Capture.PNG

13 Replies
ahaahaaha
Honored Contributor

Re: Can't format date in Load Script

Hi,

In the figure you have in the first field a different calculation formula. Where Date#()?

ahaahaaha
Honored Contributor

Re: Can't format date in Load Script

You want to apply


Date(Date#([Date], 'YYYY-MM-DD'),'DD/MM/YYYY') as Date

Not applicable

Re: Can't format date in Load Script

The first column was just me testing what would happen if I did it as a calculated dimension. I would expect the same output in the "Date" column, as I use the date# formula in the load.

sureshqv
Esteemed Contributor III

Re: Can't format date in Load Script

can u share the sample data to test?

Not applicable

Re: Can't format date in Load Script

That's what I am doing in the load script, and that is generating the second column.

dberkesacn
Contributor III

Re: Can't format date in Load Script

I would suggest this BLOG

The Date Function

Daniel

sureshqv
Esteemed Contributor III

Re: Can't format date in Load Script

what is your database date column name? 

it should be case sensitive

date(date#([date], 'YYYY-MM-DD'),'DD/MM/YYYY') as Date.

you mentioned as " Date"  so is that issue? could you please check?  as syntax is correct

Not applicable

Re: Can't format date in Load Script

Syntax is fine.

Capture.PNG

stigchel
Honored Contributor

Re: Can't format date in Load Script

What does your SET DateFormat look like in the load script? 

When it is

SET DateFormat=' YYYY-MM-DD';

Change to

SET DateFormat='DD/MM/YYYY';