5 Replies Latest reply: May 2, 2017 5:57 AM by Mindaugas Bacius RSS

    DateDiff for 2 tables and date Fields

    Marco Velde

      Good Afternoon,

       

      I am new to Qlik community and I have problems calculating the number of days between 2 fields in 2 different tables.

      I want to know the number of days between Ophef_Ontvangstdatum and Aanleg_Ontvangstdatum. ConnectionUid is the key in the table.

      Can someone deliver the sql statement for calculating the number of days between the 2 objects?

       

       

       

      LOAD Ophef_Order_nummer,
      ConnectionUid,
      Ophef_Order_type,
      Ophef_Porting_ID,
      Ophef_SERVICEID,
      Ophef_Ontvangstdatum,
      [Ophef_Active operator]
      FROM
      [C:\Users\Marco\Desktop\Softsease_Ophef_KPN.xlsx]
      (
      ooxml, embedded labels, table is Ophef);

      LOAD Aanleg_Order_nummer,
      Aanleg_SERVICEID,
      ConnectionUid,
      Aanleg_Porting_ID,
      Aanleg_Order_type,
      Aanleg_Ontvangstdatum,
      Aanleg_Active_operator
      FROM
      [C:\Users\Marco\Desktop\Softsease_Aanleg_KPN.xlsx]
      (
      ooxml, embedded labels, table is [Aanleg KPN]);

        • Re: DateDiff for 2 tables and date Fields
          Vishwarath Nagaraju

          Try like using Straight table chart

           

          Add your dimensions

           

          Expr:

          = Aanleg_Ontvangstdatum - Ophef_Ontvangstdatum

            • Re: DateDiff for 2 tables and date Fields
              Marco Velde

              But i want to add the object in the script, do you know how to edit the script so the new object is created?

                • Re: DateDiff for 2 tables and date Fields
                  Vishwarath Nagaraju

                  Can you share some sample data and what is your expected output ?

                  May be try this?

                  Table1:

                  LOAD Ophef_Order_nummer,
                  ConnectionUid,
                  Ophef_Order_type,
                  Ophef_Porting_ID,
                  Ophef_SERVICEID,
                  Ophef_Ontvangstdatum,
                  [Ophef_Active operator]
                  FROM
                  [C:\Users\Marco\Desktop\Softsease_Ophef_KPN.xlsx]
                  (
                  ooxml, embedded labels, table is Ophef);
                  JOIN
                  LOAD Aanleg_Order_nummer,
                  Aanleg_SERVICEID,
                  ConnectionUid,
                  Aanleg_Porting_ID,
                  Aanleg_Order_type,
                  Aanleg_Ontvangstdatum,
                  Aanleg_Active_operator
                  FROM
                  [C:\Users\Marco\Desktop\Softsease_Aanleg_KPN.xlsx]
                  (
                  ooxml, embedded labels, table is [Aanleg KPN]);


                  NoCocatenate

                  Final:

                  LOAD *,

                            Aanleg_Ontvangstdatum - Ophef_Ontvangstdatum AS DateDiff

                  Resident Table1;


                  Drop Table Table1;

                    • Re: DateDiff for 2 tables and date Fields
                      Marco Velde

                      Hello,

                       

                      Thanks for your help, but it is not yet the solution, because the result has to be 2 tables connected by ConnectionUID.

                      For example first table contains ConnectionUID 123 with ophef_ontvangstdatum 01-01-2015.

                      second table also contains ConnectionUID 123 with aanleg_ontvangstdatum 01-02-2015.

                       

                      Output has to be 1 connected object Connection UID, Separate fields ophef_ontvangstdatum and aanleg_ontvangstdatum and a new field DateDif that has the value 31.

                       

                      Note: There are also ConnectionUID in table 1 that are not available in Table2 and vice versa.

                        • Re: DateDiff for 2 tables and date Fields
                          Mindaugas Bacius

                          I am not sure - try this:

                          tmp:

                          LOAD Ophef_Order_nummer,
                          ConnectionUid,
                          Ophef_Order_type,
                          Ophef_Porting_ID,
                          Ophef_SERVICEID,
                          Ophef_Ontvangstdatum,
                          [Ophef_Active operator]
                          FROM
                          [C:\Users\Marco\Desktop\Softsease_Ophef_KPN.xlsx]
                          (
                          ooxml, embedded labels, table is Ophef);


                          tmp2:
                          LOAD Aanleg_Order_nummer,
                          Aanleg_SERVICEID,
                          ConnectionUid,
                          Aanleg_Porting_ID,
                          Aanleg_Order_type,
                          Aanleg_Ontvangstdatum,
                          Aanleg_Active_operator
                          FROM
                          [C:\Users\Marco\Desktop\Softsease_Aanleg_KPN.xlsx]
                          (
                          ooxml, embedded labels, table is [Aanleg KPN]);


                          left join (tmp2)

                          Load ConnectionUid

                               ,Ophef_Ontvangstdatum

                          resident tmp;


                          noconcatenate

                          tmp3:

                          Load *

                               ,Ophef_Ontvangstdatum - Aanleg_Ontvangstdatum as Diff

                          resident tmp2;


                          Drop table tmp2;

                          Drop field Ophef_Ontvangstdatum from tmp3;