topic intervalmatch? in QlikView

intervalmatch?

Mon, 28 Feb 2011 13:03:13 GMT

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.

intervalmatch?

Mon, 28 Feb 2011 14:27:41 GMT

[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.

intervalmatch?

llauses243 — Mon, 28 Feb 2011 15:16:16 GMT

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

intervalmatch?

Mon, 28 Feb 2011 15:42:46 GMT

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

intervalmatch?

Mon, 28 Feb 2011 15:53:51 GMT

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.

intervalmatch?

Tue, 01 Mar 2011 15:31:49 GMT

Thank you Aline,

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

intervalmatch?

Wed, 02 Mar 2011 19:17:09 GMT

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

intervalmatch?

Thu, 03 Mar 2011 11:14:21 GMT

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.