Iteration and Interval Match

[shwetagupta]

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

Code	Slab1	Slab2	Rates	Frequency	Effective From Date	Effective To Date
A	0	60	10,000	30	04-01-2017	03-01-2018
A	61	120	8,000	30	04-01-2017	03-01-2018
A	121	1000	6,000	30	04-01-2017	03-01-2018
B	1	1000	15,000	1	04-01-2017	03-01-2018
C	1	7	14,000	7	04-01-2017	03-01-2018
C	8	14	13,000	7	04-01-2017	03-01-2018
C	15	1000	12,000	7	04-01-2017	03-01-2018
D	0	39	13,750	1	04-01-2017	03-01-2018
D	40	1000	13,250	1	04-01-2017	03-01-2018

Code	Frequency	Bus Count
A	30	80
B	1	79
C	W1/7	8
C	W2/7	7
C	W3/7	11
C	W4/7	16
D	1	41

[shwetagupta]

swuehl

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

Thanks in Advance

Shweta Gupta

[jonathandienst]

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;

[shwetagupta]

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]

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;