MVZ MVZ - 4 months ago 37
SQL Question

Format a calculated field as Date SQL Script for Qlik Sense

Database is in oracle, my machine has odbc 32 bits configured with the name PPM and I've created a script but need assistance formatting a calculated field:

LIB CONNECT TO [PPM];

[SCRIPT]:

SQL Select NP.BUSINESS, NP.PLATFORM, NP.CATEGORY,

CASE WHEN IMPLE IS not NULL THEN IMPLE
WHEN CONTRACT IS not NULL THEN CONTRACT
WHEN ANALYSIS IS not NULL THEN ANALYSIS
ELSE IDEA END AS REALDATE,


Imple, Contact, Analysis, Idea are all fields with same format of dates and this code is needed so we can have always the last date input as "Realdate"

This gives back a correct Date in the new field (doesn't exist in the server) but shows many duplicates, using
SET TimestampFormat='MM-YYYY';
before, it gives back the dates with the correct format but shows "03-2015" 3 times and many others dates like that.
Trying also:
date(floor("REALDATE",'MM.YYYY') as "REALDATE2"
but since this is a calculated field I don't know how to make reference that field.

Thanks

MVZ MVZ
Answer

I found a way around after long hours of trying:

DATE#(DATE(Date#([REALDATE], 'MM-YYYY'), 'MMM/YYYY'), 'MMM/YYYY')

Could not format the field inside the script but this does the trick

Thanks for the help!