Qlik Community

Qlik Sense App Development

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

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
rulohx87
New 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
Highlighted
h_prakash
Contributor

Re: Range table

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
2 Replies
Partner
Partner

Re: Range table

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...
Please ekskuse my Norglish and Swenglish typos.
Highlighted
h_prakash
Contributor

Re: Range table

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