4 Replies Latest reply: Oct 31, 2011 12:57 PM by Hans Lindström RSS

    Intervalmatch issue

      Hi, I am having a problem with an interval match. My main problem is that I don´t really understand how Intervalmatch works. The result from the script below seems to be correct, but what I am struggling with is the metadata and the names of the tables.
      When I look at the table viewer the result is one "Calendar" Table, with Date as KEY connected to two tables, "DateTemp"-table and one "FlowCaseActivityTemp-1"-table. The "DateTemp"-table only contains Date.
      In other words QlikView automatically names the link-table. I would like the result to be Only one linktable connected to the "Calendar"-table and and want to be able to name that table.
      I don´t know if this makes any sence?
      It would be great if someone could look at the script below and give some comments regarding how it could be improved.
      BR//Hans
      DateTemp:
      LOAD
      Date
      Resident Calendar;

      LOAD
      StartDate,
      EndDate,
      FlowCaseActivityID
      RESIDENT FlowCaseActivityTemp;

      LEFT JOIN
      INTERVALMATCH (Date)
      LOAD
      StartDate,
      EndDate
      RESIDENT FlowCaseActivityTemp;
        • Intervalmatch issue

          hi,

          try something like:

           

          DateTemp:

          LOAD

          Date

          Resident Calendar;

           

          LINKTABLE:  // <------------------- fix the name

          LOAD

          StartDate,

          EndDate,

          FlowCaseActivityID

          RESIDENT FlowCaseActivityTemp;

           

          LEFT JOIN

          IntervalMatch(Date)

          LOAD

          StartDate,

          EndDate

          RESIDENT FlowCaseActivityTemp;

           

          drop table DateTemp;  // <------------ delete unused table

            • Intervalmatch issue

              Thanks for your answer. Actually I allready tried that without success.
              But when I change place of Date and LinkTable it worked:

              ActivityLinkTable:

              LOAD
              StartDate,
              EndDate,
              FlowCaseActivityID
              RESIDENT FlowCaseActivityTemp;

              DateTemp:
              LOAD
              Date
              Resident Calendar;

              LEFT JOIN (ActivityLinkTable)
              INTERVALMATCH (Date)
              LOAD
              StartDate,
              EndDate
              RESIDENT ActivityLinkTable;

              Drop

               

              Table DateTemp;

               

               

                • Intervalmatch issue
                  Amirali Vastani
                  try this,
                  ActivityLinkTable:
                  LOAD
                  StartDate,
                  EndDate,
                  FlowCaseActivityID
                  RESIDENT FlowCaseActivityTemp;

                  DateTemp:
                  LOAD
                  Date
                  Resident Calendar;

                  LEFT JOIN INTERVALMATCH (Date)
                  LOAD
                  StartDate,
                  EndDate
                  RESIDENT ActivityLinkTable;

                  LEFT JOIN (DateTemp)
                  LOAD * RESIDENT ActivityLinkTable;
                  Drop Table DateTemp;
                  • Intervalmatch issue

                    Hi, now I have a new problem with the same example as above.
                    The Dates I am using includes date + Time. Therefore I used:
                    Date(StartDate,'YYYY-MM-DD') as StartDate


                    Result:

                    The ActivityLink-table now skip the first date in the period, In other words:

                     

                    StartDate=2008-10-17  EndDate=2011-08-02 ( The first) Date= 2008-10-18
                    When I try and use Floor(Date(StartDate,'YYYY-MM-DD')) instead I get 6 multiple rows in the table for each row.


                    I don´t know why, do anyone have an explanation to this?

                     

                    Thanks!