4 Replies Latest reply: Oct 9, 2014 4:26 AM by Agis Kalogiannis RSS

    Intervalmatch issue

    William Choo

      Hi All,

       

      I'm trying to use Intervalmatch to map a range of days to a specific date category value e.g. 0-94 days should be mapped to "<3 months" and 341-2000 days should be mapped to ">12 months". However, I'm getting very strange results, as you can see from the screenshot below... As you can see, I expect values of 198 days to map within "7-11 months", but I don't know why it's mapping "7-11 months" even for 563 days (should be mapped to ">12 months" instead). Also, 137 days should be mapped to "3-6 months" as well instead of "7-11 months".

       

      Is there something I'm doing wrongly?? Thanks for all your help in advance.

       

      [ABC]:
      LOAD

           Customer, 

           [Item]

           [Date of Last Price],
           
      Round(Now()-Date(Date#([Date of Last Price], 'D-MMM-YY'),'MM/DD/YYYY')) as [Days Last Changed]
      FROM
      test123.csv
      (
      txt, codepage is 1252, embedded labels, delimiter is ',', msq);

       

        [Day_to_Months]:

      LOAD * INLINE

      [
      Start Days, Stop Days, Date Category, DayMonths Sort Order
      0,94,< 3 months,4
      95,186,3 - 6 months,3
      187,340,7 - 11 months,2
      341,2000,> 12 months,1
      ]
      ;
       

      IntervalMatch([Days Last Changed])

      LEFT JOIN ([ABC])

      LOAD [Start Days],[Stop Days] Resident [Day_to_Months];

      test.png

        • Re: Intervalmatch issue
          Agis Kalogiannis

          Hi William

           

          Is it possible to attach the test123.csv file you use in your script so as to have a sample of your data?

           

          Thanks

          Agis

          • Re: Intervalmatch issue
            Agis Kalogiannis

            Ok... I thing you need to change the script to the following:

             

            [ABC]:
            LOAD

                Customer,

                [Item],

                [Date of Last Price],
                 
            Round(Now()-Date(Date#([Date of Last Price], 'D-MMM-YY'),'MM/DD/YYYY')) as [Days Last Changed]
            FROM
            test123.csv
            (
            txt, codepage is 1252, embedded labels, delimiter is ',', msq);

             

              [Day_to_Months]:

            LOAD * INLINE

            [
            Start Days, Stop Days, Date Category, DayMonths Sort Order
            0,94,< 3 months,4
            95,186,3 - 6 months,3
            187,340,7 - 11 months,2
            341,2000,> 12 months,1
            ]
            ;

            IntervalMatch([Days Last Changed])

            // You need to use the Inline table that defines the intervals here and not the data table

            LEFT JOIN ([Day_to_Months])

            LOAD [Start Days],[Stop Days] Resident [Day_to_Months];


            Could you please try this and let me know?


            Ragards

            Agis

            • Re: Intervalmatch issue
              William Choo

              Hi,

               

              Actually, the statement I use below still works.. I fixed where my bug lies...It's got NOTHING to do with Intervalmatch at all, but rather all the null rows generated because of unwanted associations and wrong JOINs . I reduced the various tables and fields down to a proper snowflake schema which was most suitable for Qlikview and everything worked like a charm!

               

               

              IntervalMatch([Days Last Changed])

              LEFT JOIN ([Temp Cost Price History])

              LOAD  [Start Days],[Stop Days]      RESIDENT [Day_to_Months];

               

              Thanks for your inputs!

                • Re: Intervalmatch issue
                  Agis Kalogiannis

                  Thanks for your feedback William.

                   

                  Maybe now that you have a correct data model, could consider to transform it towards a star schema, which, in general, is much more efficient for QlikView than a snowflake schema, in terms of performance.

                   

                  Could you also please mark one of the answers above as correct or helpful?

                   

                  Thanks

                  Agis