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
