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: 
Not applicable

intervalmatch?

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.

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

7 Replies
Not applicable
Author

[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
Creator III
Creator III

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

Thank you Aline,

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

Not applicable
Author

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

Not applicable
Author

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.