Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
crichter14
Creator
Creator

SQL Load Script in Qlik Sense

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?

Code below:

 

SQL SELECT

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.

 

Cheryl

Labels (5)
1 Solution

Accepted Solutions
treysmithdev
Luminary Alumni
Luminary Alumni

Oops, should've been: 

TO_TIMESTAMP('$(var_CurrentYearStart_TS)', 'YYYY-MM-DD HH24:MI:SS')
Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

7 Replies
Carlos_Reyes
Partner
Partner

I guess this is a SQL question and not a Qlik one, isn't it?

https://stackoverflow.com/questions/23294562/convert-timestamp-to-number-format
treysmithdev
Luminary Alumni
Luminary Alumni

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.

Blog: WhereClause   Twitter: @treysmithdev
crichter14
Creator
Creator
Author

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.

crichter14
Creator
Creator
Author

Hi Trey,

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:

TO_DATE($(var_CurrentYearStart))

TO_TIMESTAMP($(var_CurrentYearStart))

CAST($(var_CurrentYearStart) AS DATETIME)

 

In SQL, I should be able to use 

Convert(int, cast(ONDATE AS datetime))
on the timestamp field, but it didn't work.  I'm assuming because Qlik doesn't like the INT or the DATETIME.
 
I appreciate any help, I've had a hard time.  We're using an ODBC connection if that helps.
treysmithdev
Luminary Alumni
Luminary Alumni

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:

TO_TIMESTAMP('$(var_CurrentYearStart_TS)', 'HH24:MI:SS')
Blog: WhereClause   Twitter: @treysmithdev
treysmithdev
Luminary Alumni
Luminary Alumni

Oops, should've been: 

TO_TIMESTAMP('$(var_CurrentYearStart_TS)', 'YYYY-MM-DD HH24:MI:SS')
Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

crichter14
Creator
Creator
Author

That worked thank you Trey!  I did have to add it to the WHERE clause too, but thank you so much!