topic Re: IntervalMatch() for 2 different facts in App Development

IntervalMatch() for 2 different facts

valpassos — Wed, 12 May 2021 19:20:35 GMT

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!

Re: IntervalMatch() for 2 different facts

Digvijay_Singh — Wed, 12 May 2021 22:41:45 GMT

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 🙂

Re: IntervalMatch() for 2 different facts

valpassos — Thu, 13 May 2021 14:02:08 GMT

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

Re: IntervalMatch() for 2 different facts

marcus_sommer — Sun, 16 May 2021 10:59:11 GMT

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

Re: IntervalMatch() for 2 different facts

valpassos — Wed, 19 May 2021 15:30:24 GMT

Thanks, @marcus_sommer !