Qlik Community

Ask a Question

App Development

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

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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
Creator

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...
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
h_prakash
Creator
Creator

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