Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
shwetagupta
Partner - Creator II
Partner - Creator II

Iteration and Interval Match

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
4 Replies
shwetagupta
Partner - Creator II
Partner - Creator II
Author

swuehl

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

Thanks in Advance

Shweta Gupta

jonathandienst
Partner - Champion III
Partner - Champion III

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
shwetagupta
Partner - Creator II
Partner - Creator II
Author

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 )

swuehl
MVP
MVP

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;