2 Replies Latest reply: Jul 9, 2017 4:53 PM by Twan Peters RSS

    Load date between 2 variables

    Twan Peters

      I've created 2 variables based on a date field and I want to use those data variables in a where clause.

       

      However, after running the scrip Qlikview messages that it can't find the used Variable fields.

       

      What am i missing here?

       

      See the attached images for a better overview of my issue.

       

      Qlikview.png

       

      Qlikview1.PNG

       

      SNAPSHOT_LAST_DAY_OF_MONTH:

      LOAD

        distinct SNAPSHOT_LAST_DAY_OF_MONTH

      FROM

      $(QVDpath)F_CONSUMER_CORE$(CurrentSnap).qvd

      (qvd);

       

      LET SNAPSHOT_MONTH=date(SNAPSHOT_LAST_DAY_OF_MONTH,'DD-MM-YYYY');

      LET SNAPSHOT_MONTH6=date(AddMonths(SNAPSHOT_LAST_DAY_OF_MONTH,-6),'DD-MM-YYYY');

       

      Test_Table:

      LOAD

      ACTIVITY_ID,

      ACTIVITY_TYPE,

      ACTIVITY_DATE,

      date(ACTIVITY_DATE,'DD-MM-YYYY') as ACTIVITY_DATE_MONTH,

      1 as test,

      CONSUMER_ID_FK

      Resident tmpEloqua

       

      Where ACTIVITY_DATE_MONTH <= $(SNAPSHOT_MONTH) and ACTIVITY_DATE_MONTH >$(SNAPSHOT_MONTH6);

       

      drop table tmpEloqua;

      Exit Script;

        • Re: Load date between 2 variables
          Sunny Talwar

          May be try this

           

          SNAPSHOT_LAST_DAY_OF_MONTH:

          LOAD Max(SNAPSHOT_LAST_DAY_OF_MONTH) as MaxDate

          FROM $(QVDpath)F_CONSUMER_CORE$(CurrentSnap).qvd (qvd);

           

          LET SNAPSHOT_MONTH = Peek('MaxDate');

          LET SNAPSHOT_MONTH6 = Num(AddMonths(Peek('MaxDate'), -6));

           

          Test_Table:

          LOAD ACTIVITY_ID,

              ACTIVITY_TYPE,

              ACTIVITY_DATE,

              Date(ACTIVITY_DATE, 'DD-MM-YYYY') as ACTIVITY_DATE_MONTH,

              1 as test,

              CONSUMER_ID_FK

          Resident tmpEloqua

          Where ACTIVITY_DATE_MONTH <= $(SNAPSHOT_MONTH) and ACTIVITY_DATE_MONTH >$(SNAPSHOT_MONTH6);

           

          DROP Table tmpEloqua;

          Exit Script;

          • Re: Load date between 2 variables
            Twan Peters

            Thank you very much!