Help with the join

Anonymous — Wed, 16 Feb 2011 18:33:36 GMT

I have 2 qlikview tables one for available hours and the other for used hours ,

each have its own diemensions , i need to have filters on DIM_DATE , TESTER_CATEGORY to calculate utilization rate which is usedhours/available hours , i get the correct value only if i select 4 values from the filters for dim_date,tester_category ( 1 dim_date from available hours table , 1 dim_date from used_hours table , 1 tester_category from available hours , 1 tester_category from used hours) , how do i handle this , i have a join on dim_date and tester_categpry i get a synthetic key .

Please Help,

here are my 2 table's query

TESTER_USEDHOURS:
QUALIFY *;
UNQUALIFY DATE_KEY,TESTER_KEY,TIME_KEY,OPERATION_KEY,HOUR_SEG_PRIME;
SQL SELECT nvl(F.LOGGED_TIME,0)/60 AS USED_HOURS,D.DATE_KEY ,O.OPERATION_KEY,o.operation_name,
t.tester_category,t.tester_name,h.hour_seg_prime,d.sun_week_start_date,DIM_TIME_KEY AS "TIME_KEY",T.TESTER_KEY
FROM "EDW_FACT"."FCT_TESTER_UTIL_OPEN" F
LEFT OUTER JOIN DIM_DATE D
ON F.DIM_DATE_KEY = D.DATE_KEY
LEFT OUTER JOIN DIM_TESTER T
ON F.TESTER_KEY = T.TESTER_KEY
LEFT OUTER JOIN DIM_TIME H
ON F.DIM_TIME_KEY = H.TIME_KEY
LEFT OUTER JOIN DIM_OPERATION O
ON F.OPERATION_KEY = o.operation_key
union all
SELECT nvl(F.LOGGED_TIME,0)/60 AS USED_HOURS,DIM_DATE_KEY,OPERATION_KEY,o.operation_name,
t.tester_category,t.tester_name,h.hour_seg_prime,d.sun_week_start_date,DIM_TIME_KEY AS "TIME_KEY",T.TESTER_KEY
FROM "EDW_FACT"."FCT_TESTER_UTILIZATION" F
LEFT OUTER JOIN DIM_DATE D
ON F.DIM_DATE_KEY = D.DATE_KEY
LEFT OUTER JOIN DIM_TESTER T
ON F.TESTER_KEY = T.TESTER_KEY
LEFT OUTER JOIN DIM_TIME H
ON F.DIM_TIME_KEY = H.TIME_KEY
LEFT OUTER JOIN DIM_OPERATION O
ON F.OPERATION_KEY = o.operation_key;

store TESTER_USEDHOURS INTO LOAD TESTER_USEDHOURS.USED_HOURS.qvd;

Available_Hours:
QUALIFY *;
Unqualify DATE_KEY,TESTER_KEY;
SQL SELECT
A.TESTER_NAME as TESTER,
A.TESTER_CATEGORY,
A.TESTER_KEY,
A.TESTER_VENDOR,
A.TESTER_VENDOR||' | '||A.TESTER_NAME as TESTER_VENDOR_AND_NAME,
A.TESTER_TYPE,
DIM_DATE.DAY_DATE as DAY_DATE,
DIM_DATE.DATE_KEY,
A.TESTER_DECOMMISSION_DATE,
A.TESTER_INCEPTION_DATE,
case when A.LOCATION <> 'Singapore'
THEN
(case when DIM_DATE.IS_HOLIDAY ='1' then 'Holiday'
when DIM_DATE.IS_WEEK_END='0' then 'Weekday'
when DIM_DATE.IS_WEEK_END='1' then 'Weekend'
end)
ELSE
(case when DIM_DATE.IS_HOLIDAY_SINGAPORE ='1' then 'Holiday'
when DIM_DATE.IS_WEEK_END='0' then 'Weekday'
when DIM_DATE.IS_WEEK_END='1' then 'Weekend'
end)
END DAY_TYPE,
substr(DIM_DATE.GRG_YEAR_QTR,1,4)||' - '||substr(DIM_DATE.GRG_YEAR_QTR,8,2) YEAR_QTR,
DIM_DATE.DAY_IN_WEEK_NAME as WEEK,
DIM_DATE.SUN_WEEK_START_DATE as SUN_WEEK_START_DATE,
to_char(DIM_DATE.SUN_WEEK_START_DATE,'yyyy-MM') YEAR_MONTH,
substr(DIM_DATE.GRG_YEAR_QTR,1,4) YEAR,
B.HOUR_SEG_PRIME,
(case
when B.HOUR_SEG_PRIME='AM-OS:12 AM - 7 AM' then 0.292
when B.HOUR_SEG_PRIME='AM-Eng:7 AM - 9 AM' then 0.083
when B.HOUR_SEG_PRIME='Prime Time:9 AM - 7 PM' then 0.417
when B.HOUR_SEG_PRIME='PM-Eng:7 PM - 10 PM' then 0.125
when B.HOUR_SEG_PRIME='PM-OS:10 PM - 12 AM' then 0.083
end)*
24 as Available_Hours
FROM
(SELECT
DIM_TESTER.TESTER_NAME,
UPPER(TRIM(DIM_TESTER.TESTER_CATEGORY)) TESTER_CATEGORY,
DIM_TESTER.TESTER_VENDOR,
DIM_TESTER.TESTER_TYPE,
DIM_TESTER.TESTER_KEY,
DIM_TESTER.LOCATION,
DIM_TESTER.TESTER_DECOMMISSION_DATE,
DIM_TESTER.TESTER_INCEPTION_DATE

FROM
DIM_TESTER) A,
DIM_DATE,
(select
distinct hour_seg_prime
from dim_time) B
where
DIM_DATE.DAY_DATE >= A.tester_inception_date AND DIM_DATE.DAY_DATE < A.TESTER_DECOMMISSION_DATE

Help with the join

Anonymous — Wed, 16 Feb 2011 20:33:14 GMT

can anybody please respond ,

topic Help with the join in QlikView

Help with the join

Help with the join