If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
Question specifics: Answers must be for Qlik Sense not Qlik View, please. This is in the load script, not in an object. I have a variable var_CurrentYearStart compared to a field AUDTIME. It's not going to work, because the AUDTIME field is a timestamp and the variable is a number (#####).
In the SQL load script, how can I change the AUDTIME timestamp to a numeric value that I can use. The normal Qlik Syntax won't work because DATE and NUM are not recognized by the SQL. Ideas?
ROWNUM AS AUDIT_ID,
C.COL AS AUDIT_FIELDNAME,
T.AUDTIME AS AUDIT_TIME
T.TBL AS AUDIT_TABLENAME,
FROM AUD_COL C
LEFT JOIN AUD_TBL T ON C.AUD_TBLIDX=T.AUD_TBLIDX
WHERE T.AUDTIME >=$(var_CurrentYearStart) AND T.TBL IN ('SORD','SORDD','SOZD') AND C.COL IN ('ALLOQ','PACKQ');
Thanks guys, I'm struggling with this one.
This depends on the database you are querying, since different vendors have different functions to convert datatypes.
Can you convert the variable to a timestamp? It would be faster converting the one variable to a timestamp rather than the database convert every value in the field during its comparison. You can do the same through SQL by wrapping $(var_CurrentYearStart) with the appropriate SQL function.
Close Carlos, but I need it to be a number like the format of a NUM(DATE(field)) in Qlik. This number comes back like 20190723 but I need it to be 43669. Thank you for responding.
I've tried variations, and code that should work isn't. I think it's because its an SQL where clause rather than Qlik so it's not working. These are my fails:
CAST($(var_CurrentYearStart) AS DATETIME)
In SQL, I should be able to use
Let's convert your variable to the timestamp string first:
Let var_CurrentYearStart = Floor(YearStart(Today())); Trace ------------------------------- Year Start Num: $(var_CurrentYearStart); Let var_CurrentYearStart_TS = Text(Timestamp($(var_CurrentYearStart),'YYYY-MM-DD hh:mm:ss')); Trace ------------------------------- Year Start TimeStamp: $(var_CurrentYearStart_TS);
Then in your SQL query you can cast a CHAR to a TIMESTAMP: