Loading datetime fields from SQL

[Anonymous]

Hello,

I have hacked together the following basic script

LIB CONNECT TO 'MF';

SQL SELECT * FROM Transport_Comp3.dbo.JobItem WHERE dtCollectionDate>'2018-07-01' and bInvoiced=1;

Could anyone advise what I need to do so that Qlik Sense Desktop will recognise datetime fields in SQL as date fields in Qlik? It doesn't matter if the time in SQL is lost, I really just want the date section.

Thanks

[OmarBenSalem]

Did u had an error?

if u do : year(dtCollectionDate) what'll be the result?

please, answer these 2 questions to know how to proceed from there on

[arvind1494]

Table1:

SQL SELECT * FROM Transport_Comp3.dbo.JobItem ;

Table2:

load * resident Table1 WHERE num(dtCollectionDate) >num('2018-07-01') and bInvoiced=1;

Drop Table1;

[jonathandienst]

By default, date types in SQL are recognized as dates by Qlik, Is your question about the dates in Qlik or how to add a date from Qlik to the SQL Select Where clause?

[Anonymous]

Sorry the where clause is probably not helpful here. It is working fine.

My question is whenever I go to edit an App, in my list of fields, none of the datetime fields that I have imported are being recognised as such.

When I went through the "Add data, create a new connection" wizard,  and did an import, the same fields were recognised as dates. I'm choosing to use the script as I've got quite a lengthy SQL query the pass the data through. Do I need to do something special in the query, or should "SELECT *" be sufficient for datetime field to be imported as such.

[sewialwork]

Hello!

To transform yout data in Qlik Sense you should use smth like this https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/FormattingFunctions/form...

So your scrips should look smth like this (in one table, yes):

[Table1]:

//here is your transformation part with qlik sense syntax

LOAD date(dtCollectionDate) as Date,

month(dtCollectionDate) as [Month],

year(dtCollectionDate) as [Year],

Abbreviation as [SMTHSMTH];

//here is your sql and condition parts

SELECT * from Transport_Comp3.dbo.JobItem WHERE dtCollectionDate >'2018-07-01' and bInvoiced=1;

For your convinience u can let Qlik write sql and condition parts for you in data selection dialog using "Filter data" string and then rewrite transformation part. Read more here: https://help.qlik.com/en-US/connectors/Subsystems/ODBC_connector_help/Content/SQLServer/Load-SQL-Ser...

[Anonymous]

Thanks. So if I understand correctly, you do the Qlik syntax before the SQL?

Using the code you gave above, I've been able to load the data, but the fields as still not coming across as date fields. Any idea why this might be?

Another question - it appears to be only the fields that are specified in the LOAD section that come through. Because of the SELECT * in SQL, I thought all fields would come through. Is this correct?

Sorry for my lack of knowledge!

[sewialwork]

So if I understand correctly, you do the Qlik syntax before the SQL?

Yes, in that order.

Using the code you gave above, I've been able to load the data, but the fields as still not coming across as date fields. Any idea why this might be?

Try date# or timestamp# (https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/InterpretationFunctions/...), combination of formating and interpritation functions (in one of my apps i had some insane combos like MakeDate(Year(A_DATE),Month(A_DATE),Day(A_DATE)) as Date or Date(Date#(Field))as Date). Check your TimestampFormat and DateFormat at the start of your script - I personally preffer to alter these for my regional standards, this also can help.

Another question - it appears to be only the fields that are specified in the LOAD section that come through. Because of the SELECT * in SQL, I thought all fields would come through. Is this correct?

Yep. You will receive fields that named  both in the first and second part of your script. So here:

Tmp:

LOAD YEAR,

     FIL_NAME as Branch2,

     left(trim(PARAM_CODE),5) as Code

Where YEAR<2020;

SQL SELECT *

FROM OUTGATE."DI_POK_NPP"

WHERE YEAR > 2016 ;

you`ll get YEAR, Branch2 and Code. But if you ll do this:

Tmp:

LOAD *,

     FIL_NAME as Branch2,

     left(trim(PARAM_CODE),5) as Code

Where YEAR<2020;

SQL SELECT *

FROM OUTGATE."DI_POK_NPP"

WHERE YEAR > 2016 ;

drop fields FIL_NAME,PARAM_CODE from tmp;

you`ll get all fields and will have some of them transformed with Qlik functions. Just dont forget to drop the ones u dont need.

It s important to remember that your SQL part will be performed in your source, and transformation part - on the Qlik server, so if you dont want to waste your resourses, minimize your datasets as much as you can. In my example the best solution will be:

Tmp:

LOAD YEAR,

     FIL_NAME as Branch2,

     left(trim(PARAM_CODE),5) as Code;

SQL SELECT distinct YEAR, FIL_NAME, PARAM_CODE

FROM OUTGATE."DI_POK_NPP"

WHERE YEAR<2020 and YEAR > 2016 ;

Hope this will help!)

AGB