Range table

[rulohx87]

Hello everyone, 

I have the following range table.

# Credit	Min	Max
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:

CODE	COST
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:

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

Thank you

[Vegar]

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 · Accepted solution]

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