4 Replies Latest reply: Sep 10, 2018 5:46 PM by Stefan Wühl RSS

    Iteration and Interval Match

    shweta gupta

      Hi Fellas,

      I am having following set of data

      where Code  is a depot,
      Slab: To keep the count of buses reaching depot

       

      Frequency: SLab will be workable for how many days

       

      i.e In a month's period if my bus count to a station A is 80 then cost will be calculated as "(60+20) i.e 60 *cost of Slab1 (0-60) + 20*Cost of slab (61-120)

      Output Required=> 60*10000+ 20*8000

      Frequency Considerations : 30 as Monthly; 7 as Weekly ; 1 as Daily

       

             

      CodeSlab1 Slab2RatesFrequencyEffective From DateEffective To Date
      A060 10,000 3004-01-201703-01-2018
      A61120 8,000 3004-01-201703-01-2018
      A1211000 6,000 3004-01-201703-01-2018
      B11000 15,000 104-01-201703-01-2018
      C17 14,000 704-01-201703-01-2018
      C814 13,000 704-01-201703-01-2018
      C151000 12,000 704-01-201703-01-2018
      D039 13,750 104-01-201703-01-2018
      D401000 13,250 104-01-201703-01-2018

       

       

         

      CodeFrequencyBus Count
      A3080
      B179
      CW1/78
      CW2/77
      CW3/711
      CW4/716
      D141
        • Re: Iteration and Interval Match
          shweta gupta

          swuehl

          Hi, Can you please help me on the case mentioned above. Looking forward for your reply

           

          Thanks in Advance

          Shweta Gupta

            • Re: Iteration and Interval Match
              Jonathan Dienst

              Like this:

               

              Data:

              LOAD .... fact table fields including [Slab]....

              FROM ....;

               

              MatchTable:

              LOAD Code, Slab, Slab2, Rates, Frequency

              FROM ....;

               

              IntervalMatch(Slab, Code)

              Left Join (Data)

              LOAD Slab1, Slab2, Code

              Resident MatchTable;

               

              This part is optional to simplify the data model:


              Left Join (Data)

              LOAD *

              Resident MatchTable;

               

              DROP Table MatchTable;

                • Re: Iteration and Interval Match
                  shweta gupta

                  Hi Jonathan,

                  Thanks for the reply.

                  Solution mentioned above is already there.

                   

                  Problem for me here is to calculate cost corresponding to the slabs.

                  my bus count to a station A is 80 then cost will be calculated as "(60+20) i.e 60 *cost of Slab (0-60) + 20*Cost of slab (61-120)

                  Output Required=> 60*10000+ 20*8000

                   

                  1. Slab length(Interval) is not definite , Interval changes with the depot.

                  2. No. of slabs  corresponding to station is not definite either.

                  3. Cost wont be calculated corresponding to direct slabs. (It will A+B+ C things i.e Incremental cost )

                    • Re: Iteration and Interval Match
                      Stefan Wühl

                      Maybe something along these lines (I assumed you have a date with your facts that you need to match with effective ranges, use Intervalmatch() with an appropriate key for this. Then I joined all appropriate rate lines to each fact line and calculated the Bus Count ranges falling into the slabs. Aggregating the lines per fact line multiplied with the ranges should give you your numbers):

                       

                      SET ThousandSep=',';

                      SET DecimalSep='.';

                      Set DateFormat = 'DD-MM-YYYY';

                       

                       

                      Rates:

                      LOAD Code,

                           Slab1,

                           Slab2,

                           Rates,

                           Frequency,

                           [Effective From Date],

                           [Effective To Date],

                           Recno() as RateID

                      FROM

                      [https://community.qlik.com/thread/313334]

                      (html, codepage is 1252, embedded labels, table is @1);

                       

                       

                      Facts:

                      LOAD Code,

                           Frequency as FactFrequency,

                           [Bus Count],

                           Recno() as FactRow,

                           '01-01-2018' as FactDate

                      FROM

                      [https://community.qlik.com/thread/313334]

                      (html, codepage is 1252, embedded labels, table is @2);

                       

                       

                      IntervalMatch:

                      INNER JOIN

                      IntervalMatch(FactDate, Code) // or any appropriate key (combination of Code and Frequency?)

                      LOAD  [Effective From Date],

                           [Effective To Date],

                           Code, RateID

                      Resident Rates;

                       

                       

                      LEFT JOIN (Facts)

                      LOAD * Resident Rates;

                       

                       

                      DROP TABLE Rates;

                       

                       

                      FactsDistributed:

                      LOAD Only(Code) as Code, FactRow, Num(Sum(Rates*Diff)) as Amount, Only([Bus Count]) as [Bus Count]

                      Group By FactRow;

                      LOAD Code,

                           FactFrequency,

                           [Bus Count],

                           FactRow,

                           FactDate,

                           RangeMin([Bus Count], Slab2) - RangeMin([Bus Count], RangeMax(Slab1-1,0)) as Diff,

                           Rates

                      Resident Facts;

                       

                       

                      DROP TABLE Facts;