IntervalMatch() for 2 different facts

[valpassos]

Hi community,

I have 2 numbers in my fact table that I just need to compare against an intervals table.

IntervalTable:
LOAD
"From",
"To",
"Class"
FROM MyIntervalsDataSource;


FactTable:
LOAD
...
...
#Number1
#Number2
From IntervalTable;

I just did this:

BridgeTable:
IntervalMatch(#Number1)
LOAD
"From",
"To"
RESIDENT IntervalTable;

But now, how do I do the same to #Number2?

And I need to differentiate between my #Number1 and #Number2 in the front-end - that is, I need to add a Type field that tells me that when I'm seeing #Number1, I'm actually seeing a certain type of number, and the same for #Number2. Otherwise, they are just numbers.

How can I accomplish this?

Thank you!

[Digvijay_Singh]

I am not sure if I totally understand the requirements but just an idea, what if you do interval match twice by making two bridge tables one with intervalmatch(#Number1) and Intervalmatch(#Number2). I know it would create multiple fact rows out of one single IntervalTable rows which may or may not be impacting other things. You may do resident load further to create type fields in the newly created bridge tables.

I know lots of if and but, thats all I got as of now 🙂

[valpassos]

Hi Digvijay,

Thanks for your input. That was actually what I did, but I'm not getting results:

IntervalTable:
LOAD 
"From",
"To",
"Class"
FROM MyIntervalsDataSource;

FactTable:
LOAD
	...
	...
	#Number1,
	#Number2,
       [#Number1]&'|'&[#Number2] AS %ID_Interval
From IntervalTable;

BridgeTable1:
IntervalMatch(#Number1)
LOAD  
"From",
"To"
RESIDENT IntervalTable;

NoConcatenate
BridgeTable2:
IntervalMatch(#Number2)
LOAD  
"From",
"To"
RESIDENT IntervalTable;

NoConcatenate
BridgeTableTmp:
LOAD *
,'Issue'					AS Interval_Type
RESIDENT BridgeTable1;

CONCATENATE(BridgeTableTmp)
LOAD *
,'Payment'					AS Interval_Type
RESIDENT BridgeTable2;

DROP TABLE BridgeTable1;
DROP TABLE BridgeTable2;

BridgeTable:
LOAD 
    [#Number1]&'|'&[#Number2] AS    %ID_Interval,
    Interval_Type,
    From,
    To
RESIDENT BridgeTableTmp;
DROP TABLE BridgeTableTmp;

But this is not linking anything.

My requirement is simple: I just need to locate my two numbers in a range of intervals (0-30, 31-60,..) so I can use the buckets as a dimension in the front-end.

Anything else comes to your mind?

Thanks!

Lisa

[marcus_sommer · Accepted solution]

I think I would do the following:

m: mapping load (From + iterno() -1), Class
from Interval while (From + iterno() -1) <= Ende;

facts: load *,
                      applymap('m', #Number1, '#NV') as Class1,
                      applymap('m', #Number2, '#NV') as Class2
            from facts;

- Marcus

[valpassos]

Thanks, @marcus_sommer !