Skip to main content

App Development

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

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
rulohx87
Contributor III
Contributor III

Range table

Hello everyone, 

I have the following range table.

# CreditMinMax
1 $                  29.00 $                        200.00
2 $                200.00 $                        500.00
3 $                500.00 $                    1,000.00
4 $            1,000.00 $                    2,000.00

 

This the Fact Table:

CODECOST
BGF152215-3 
GS19241-3 $       890.00
CH63419-3 $       590.00
GS19019-9 $       590.00
GS19241-B $       890.00
CH63395-3 $       890.00
GS19242-B $       990.00
CH63369-3 $       690.00
CH63418-3 $       790.00
CH63421-3 $       140.00
CH63381-3 $       290.00
GS19016-R $       990.00
GRF152236-P $       320.00
GF16184-3 $          29.00

 

How do I get the credit number from the cost? I need the following table:

 

CODECOST# Credit
BGF152215-3  
GS19241-3 $       890.003
CH63419-3 $       590.003
GS19019-9 $       590.003
GS19241-B $       890.003
CH63395-3 $       890.003
GS19242-B $       990.003
CH63369-3 $       690.003
CH63418-3 $       790.003
CH63421-3 $       140.002
CH63381-3 $       290.002
GS19016-R $       990.003
GRF152236-P $       320.002
GF16184-3 $          29.001

 

Thank you

Labels (3)
1 Solution

Accepted Solutions
h_prakash
Creator II
Creator II

Hi,

Assuming Fact Table and Range Table is already loaded. try the below script.

RangeTable1:
IntervalMatch(Cost)
Load Min,
 Max
Resident RangeTable;
 
 
Left Join(Fact Table)
Load "Time",
MinTimeStamp,
MaxTimeStamp,
Min&'-'&Max as MinMaxKey
Resident RangeTable1;
Drop Table RangeTable1;
 
Left Join(Fact Table)
Load Min&'-'&Max as MinMaxKey,
[# Credit]
    Resident RangeTable;
    Drop Table RangeTable;
 
Thanks
Prakash

View solution in original post

2 Replies
Vegar
MVP
MVP

You should explore interval match.

Load * from RangeTable;
Load * from FactTable;
Intervalmatch (COST ) load Min,Max
from RangeTable;

Read more here: https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/LoadData/matching-in...
h_prakash
Creator II
Creator II

Hi,

Assuming Fact Table and Range Table is already loaded. try the below script.

RangeTable1:
IntervalMatch(Cost)
Load Min,
 Max
Resident RangeTable;
 
 
Left Join(Fact Table)
Load "Time",
MinTimeStamp,
MaxTimeStamp,
Min&'-'&Max as MinMaxKey
Resident RangeTable1;
Drop Table RangeTable1;
 
Left Join(Fact Table)
Load Min&'-'&Max as MinMaxKey,
[# Credit]
    Resident RangeTable;
    Drop Table RangeTable;
 
Thanks
Prakash