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

# 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

 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
• ###### Re: Iteration and Interval Match

swuehl

Shweta Gupta

• ###### Re: Iteration and Interval Match

Like this:

Data:

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

FROM ....;

MatchTable:

LOAD Code, Slab, Slab2, Rates, Frequency

FROM ....;

IntervalMatch(Slab, Code)

Left Join (Data)

Resident MatchTable;

This part is optional to simplify the data model:

Left Join (Data)

Resident MatchTable;

DROP Table MatchTable;

• ###### Re: Iteration and Interval Match

Hi Jonathan,

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

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:

Slab1,

Slab2,

Rates,

Frequency,

[Effective From Date],

[Effective To Date],

Recno() as RateID

FROM

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

Facts:

Frequency as FactFrequency,

[Bus Count],

Recno() as FactRow,

'01-01-2018' as FactDate

FROM

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

IntervalMatch:

INNER JOIN

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

[Effective To Date],

Code, RateID

Resident Rates;

LEFT JOIN (Facts)

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;

FactFrequency,

[Bus Count],

FactRow,

FactDate,

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

Rates

Resident Facts;

DROP TABLE Facts;