7 Replies Latest reply: Aug 22, 2018 6:57 AM by Alexandra Bragina RSS

    Loading datetime fields from SQL

    Andrew Morrison

      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

        • Re: Loading datetime fields from SQL
          omar bensalem

          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

          • Re: Loading datetime fields from SQL
            arvind pednekar

            Table1:

            SQL SELECT * FROM Transport_Comp3.dbo.JobItem ;



            Table2:

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

            Drop Table1;

            • Re: Loading datetime fields from SQL
              Jonathan Dienst

              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?

                • Re: Loading datetime fields from SQL
                  Andrew Morrison

                  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.

                    • Re: Loading datetime fields from SQL
                      Alexandra Bragina

                      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/formatting-functions.htm

                       

                      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-Server-data.htm

                       

                        • Re: Loading datetime fields from SQL
                          Andrew Morrison

                          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!

                            • Re: Loading datetime fields from SQL
                              Alexandra Bragina

                              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/interpretation-funct…), 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