Qlik Community

Qlik Sense App Development

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

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
NemoAndStitch22
New Contributor III

Interval Match/inline - Vlookup true

Hiya,

I'm essentially trying to do a vlookup true in Qlikesense based on a value column in my main Data load file. 

I have a Value range Mapping with the info.

Value Brackets.PNG

I'm unable to make Interval Match work, this was my script:

[Value Range]:
LOAD
['Negative/Zero'],
['0-250'],
['251-500'],
['501-1000'],
['1001-2500'],
['2501-5000'],
['5000+'];


LOAD
Value,
"Value Range Category"
FROM [lib://Lookup/Lookup.xlsx]
(ooxml, embedded labels, table is [Value Range]);

[interval match table]:
IntervalMatch([Value Range])
Load

['0-250'],
['251-500'],
['501-1000'],
['1001-2500'],
['2501-5000'],
['5000+']
Resident ['Value Range'];

 

I then searched some more and found out about an incline load, so have removed the above script for this:

Test:
Load * Inline [
from, to, 'Value Range'
-999999, 0, "Negative/Zero"
0.01, 250, "0-250"
250.01, 500, "250-500"
500.01, 1000, "500-1000"
1000.01, 2500, "1000-2500"
2500.01, 5000, "2500-5000"
5000.01, 1000000000, "5000+" ];

Data:
Load "Value"
FROM [lib://Surveyor Job Ledger Load Files/SJL*]
(ooxml, embedded labels, header is 6 lines, table is SJL);

Left Join (Data)
IntervalMatch (Value)
Load from, to
Resident Test;
Left Join (Data)
Load * Resident Test;
Drop Table Test

 

This script loaded fine, but I am getting Total value of £8 million, when it should be around £1 million. 

If I just do a simple table I get the total value of £1,173,778 which is correct, however if I do a table with the Value ranges, It's giving a total value of £8,281,960.28.

vaklue.PNG

Please help! I feel like I'm going mad!

 

Thanks