Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NemoAndStitch22
Contributor III
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 don't understand what's going wrong with my value bracket part of the script to cause this.

Thanks

Labels (3)
1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

Try 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);


IntervalMatch(Value)
Left Join(Test)
Load from, to
Resident Test;

Drop fields from, to;

View solution in original post

1 Reply
Lisa_P
Employee
Employee

Try 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);


IntervalMatch(Value)
Left Join(Test)
Load from, to
Resident Test;

Drop fields from, to;