Discussion Board for collaboration related to QlikView App Development.
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.
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.
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
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.
Thank you Aline,
Well i lost few nulls. But its not solve my problem fully. anyway thanks again.
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
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.