2 Replies Latest reply: Oct 23, 2018 7:42 AM by Martijn Van Eekelen RSS

    Loading data where date = Today()+1 (tomorrow)

    Martijn Van Eekelen

      Hi everyone,

       

      I'm trying to load only the data where my date (Leverdatum) is tomorrow.

      I have tried "Where Leverdatum = Today()+1"  and other options/ways i've found like "'$(Today()+1)'".


      When i try to load this i get the following error:

       

      De volgende fout is opgetreden:

      Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 4294962067, ErrorMsg: [PSQL][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Invalid user-defined or scalar function.

      De fout is hier opgetreden:

      SQL SELECT *

      FROM Ord

      Where Leverdatum = Today(0)+1


      This is my load script:

      LOAD

      OrderNummer,

          Debiteur,

          RouteNr,

          Naam as Debiteurnaam,

          InpaklijstGeprint,

          OrgOrd as Backorder_van,

          Referentie,

          StatPost,

          Leverdatum,

          WssFlag,

          NaamIngever as IngeverOrderKop;

       

      SQL SELECT *

      FROM Ord

      Where Leverdatum = Today()+1;

       

      When i try this with a static date it does work.

      E.g. "Where Leverdatum = '43396';"

       

      I have tried other things but as i am pretty new to this i'm not sure they even make sense so I haven't included them all to keep this from getting very long and confusing.

       

      Hope someone can point me in the right direction.

      Thanks!

        • Re: Loading data where date = Today()+1 (tomorrow)
          Nicole Smith

          It's because you're putting it in your SQL code but you're writing Qlik code.  SQL doesn't know what Today() is.

           

          LOAD
          OrderNummer,
              Debiteur,
              RouteNr,
              Naam as Debiteurnaam,
              InpaklijstGeprint,
              OrgOrd as Backorder_van,
              Referentie,
              StatPost,
              Leverdatum,
              WssFlag,
              NaamIngever as IngeverOrderKop;
          
          SQL SELECT *
          FROM Ord
          Where Leverdatum = CAST(GETDATE()+1 AS DATE);
          
            • Re: Loading data where date = Today()+1 (tomorrow)
              Martijn Van Eekelen

              Hi Nicole,

               

              Thanks, that's good to know.

               

              However with "CAST(GETDATE()+1 AS DATE)" I still get the same error.

               

              De volgende fout is opgetreden:

              Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 4294962067, ErrorMsg: [PSQL][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Invalid user-defined or scalar function.

              De fout is hier opgetreden:

              SQL SELECT *

              FROM Ord

              Where Leverdatum = CAST(GETDATE()+1 AS DATE)

               

              Edit:

              After some more searching i've come up with this, which seems to work for now.

               

              LET vTomorrow = Date#(Today()+1);

               

              LOAD

              OrderNummer,

                  Debiteur,

                  RouteNr,

                  Naam as Debiteurnaam,

                  InpaklijstGeprint,

                  OrgOrd as Backorder_van,

                  Referentie,

                  StatPost,

                  Leverdatum,

                  WssFlag,

                  NaamIngever as IngeverOrderKop;

                

              SQL SELECT *

              FROM Ord

              Where Leverdatum = $(vTomorrow);