Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
valpassos
Creator II
Creator II

IntervalMatch() for 2 different facts

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!

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

4 Replies
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
Creator II
Creator II
Author

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
MVP & Luminary
MVP & Luminary

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

View solution in original post

valpassos
Creator II
Creator II
Author

Thanks, @marcus_sommer !