intervalmatch?

[#1]

hello all.

im new in QV please help.

i have 2 table.

table1:

load * inline [

id, pack_weight, type

01, 0.2, express

02, 0.9, normal

03, 1.2, normal

....

];

table2:

load * inline [

type, weight, cost

normal, 05, 5

normal, 1, 10

normal, 1.5, 15

....

express, 1, 15

express, 2, 30

express, 3, 35

....

];

then i used intervalmatch to match weights.

match:

load * inline [

start,end,weight

0.1,0.5,0.5

0.6,1,1

1.1,1.5,1.5

.....

];

temp:

intervalmatch(pack_weight) load start,end resident match;

left join(temp) load * resident match;

drop table match;

left join(table1) load * resident temp;

drop table temp;

as u can see express weight start from 1kg. so its give me null under 0.5kg pack_weights.

what should i do? help please.

ps: i dont want to change or edit table2's values.

[#2]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.04.88.64/intervalmatch-test.qvw]

here is the sample.

also there is some nulls

Please help this.

[llauses243]

Hi,

I'm not understandind your case, please see image attached

Null ok, do you must to use values for Cost or option check for omitted rows

Good luck, Luis

[#4]

Table 2 & Temp both have a field named Weight. Because of this, when it does a join, it will match on that field name and only do the exact matches. This wilI lose the functionality of using an IntervalMatch. I would change the name in the Temp table to something like Category_Weight. It should resolve this.

Thanks,

Aline

[#5]

Hello Luis

intervalmatch matchin table1 and temp tables.

so table1 is like:

id, type, pack_weight, weight, start, end

01, express, 0.2, 0.5, 0.1, 0.5

02, normal, 0.9, 1, 0.6, 1

.....

but in table2 there isn't 0.5kg in express type that's why its showing null.

i need to match express type begins 1kg from temp table.

Also there is some null inserted end of table after i used intervalmatch. And i want to use straight chart and sum(cost) but end of chart there is null and its add some costs.

Sorry for my bad explain and English.

[#6]

Thank you Aline,

Well i lost few nulls. But its not solve my problem fully. anyway thanks again.

[#7]

I looked at this more closely. You are getting nulls because you are trying to do this with 2 dimensions Weight & Type. Notice that the nulls fall where there is not a definition for the combination of weight & type (ie: 0.2 falls into .5, but Express does not have anything defined for that). I would change your IntervalMatch statement to and Extended match.

Thanks,

Aline

[#8 · Accepted solution]

Hi Aline.

Thanks for your time. Problem is solved.

fact:
load * inline [
id, type, weight
1047, normal, 0.4
2025, express, 0.3
1082, normal, 1.8
2093, express, 2.1
];

weight_for_express:
load * inline [
start, end, weight_sort, type
0.1, 0.5, 0.5, express
0.6, 1, 1, express
1.1, 1.5, 1.5, express
1.6, 5, 5, express
];

weight_for_normal:
load * inline [
start, end, weight_sort, type
0.1, 1, 1, normal
1.1, 2, 2, normal
2.1, 3, 3, normal
3.1, 4, 4, normal
];

FactsWithWeights: // This table will be the final one.
INTERVALMATCH (weight, type) LOAD
 start,
 end,
 type
RESIDENT weight_for_express;
LEFT JOIN LOAD *
RESIDENT fact;

DROP TABLE fact;
DROP TABLE weight_for_express;

Thanks to Miguel.