master calendar errors

[ddd111rgg23]

hello

i try to link all data using master calendar,  but when i try to edit the query there is error in loading 

this is my query:

thank you 

[rubenmarin]

Hi, probably because of:

// Date(Date#(TRANSACTION_DATE,'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY') as Master_Calendar_Date_Num,

The Date() expresson is commented but the field name not, you'll need to comment also Master_Calendar_Date_Num, as this field won't exists in the table.

[ddd111rgg23]

 i need to define transaction date as mastercalender to link it with the other data, 

i comment the rows because when i there is error when i load data 

[marksouzacosta]

Hi @ddd111rgg23,

What is the error message?
Can you please also share a sample of the TRANSACTION_DATE field original values? A screen shot from the database will be fine.

Regards,

Mark Costa

[ddd111rgg23]

please find the data from transaction date 

[rubenmarin]

Hi, you can try with:

Date(Floor(Timestamp#(Time,'YYYY-MM-DD hh:mm:ss.ffffff')),'DD/MM/YYYY') as Master_Calendar_Date_Num

Or: Date(Floor(Timestamp#(Left(Time,19),'YYYY-MM-DD hh:mm:ss')),'DD/MM/YYYY') as Master_Calendar_Date_Num

[marcus_sommer · Accepted solution]

date() and date#() are Qlik functions but you applies them to the sql-statement and it's very likely that you data-base doesn't have adequate functions. The solution here is quite simple - just apply this within a preceding load, which means:

load *, date(...) as X;
sql select .... from Y;

[ddd111rgg23]

thank you

[ddd111rgg23]

thank you

[ddd111rgg23]

thank you