Join two interval fields from same table

[Haakan]

Hi!

I have two dim tables, a fact tables and a excel sheet with "rules". The fact table is linked by keys to the dim tables.

DimTablesA:
%A
Aid

DimTableB:
%B
Bid

FactTable:
%A
%B
Sales

The excelsheet look like this:

RowNo;intervalAid;IntervalBid;SumRowNo
101;334455-334900;0-99;
102;456666-470000;34-40;
103;501111-569999;60-65;
104;501112;60-65;
105;;;101-103

The resulting table would be
101;sum of sales corresponding to intervalA and intervalB
102;sum of sales corresponding to intervalA and intervalB
....
105;sum of rows 101-103(thus row 104 is a part of the sums of row 103)

I have manage to left join and intervalmatch(Aid) but then i got stuck!

I been thinking of a composite key but i cannot figure it out how to do this!

Any hints and/or help appreciated

Best Regards

[marcus_sommer]

I think I would create an appropriate mapping-table per while-loops - probably something in this  direction:

m: mapping
load a & '|' & b, Rule;
load *, bStart + iterno() - 1 as b while bStart + iterno() - 1 < bEnd;
load *, aStart + iterno() - 1 as a while aStart + iterno() - 1 < aEnd;
load Rule, aStart, aEnd, bStart, bEnd from Excel;

t: load *, applymap('m', %A & '|' & %B, 'myDefault') as Rule from Facts;

[Haakan]

Thank you Marcus for your answer. However I not quite sure how to handle the mapping table - is this the right
way to handle the mapping table?

mapRad:
Mapping
Load
AId& '|' &BId,
Rule
Resident Excel;

unfortunately I don't get the result I want.

[marcus_sommer]

The mapping-table is as load-statement correct - but you couldn't load the data purely from the Excel else the intervals needs to be resolved to single-values which are then finally combined as lookup-value which could be later queried per applymap().

The above shown approach with the two internal while-loops is logically the same like the intervalmatch load-prefix but IMO simpler and more flexible/powerful especially if there are multiple fields and/or extra conditions.

I suggest that you comment everything unless the Excel load and there also the mapping-prefix to get the results visible within the UI. Further you may add recno(), rowno() and the both iterno() to the load to get each kind of record-id which enables you to track the resolving-logic on the lowest granularity. 

[Haakan]

Ok Thanks - I try my best!