I defy you all to translate the following SQL query, which has run beautifully in my report for months, into the usual QlikView joins. I've basically wasted a work week trying to figure this out.
The work_items table is automated to delete work_items that are more than 3 weeks old to save space. However, management wants the report to gather historical work_items which will be saved elsewhere (actually in a qvw file on our server)
I'll spot you an already-loaded Work_Items resident table; you needn't work out the gory details of loading from a qvd.
Work_Items: Load id as wi_id, customer_id, loan_id, type_cd, created_on, updated_on, resolved_on ;
OK, here's the SQL code. It aggregates work_item data (filtered by type_cd) and groups by date. However business rules dictate that a work_item that is created on the created_on date must be counted for each day (inclusive) between its created_on date and the resolved_on date. All denorm_next_business_dates does is give us a mini-cartesian results set -- literally the only field in there that's useful is 'date'.
Yes, I know I'm bounding in the where clause by the last-two-weeks, but I'm trying to verify to myself that the numbers generated by QlikView joins will match those generated from the SQL joins. Once the QlikView join script works, we'll remove those artificial bounds and work over the entirety of work_items date range data.
WorkItemTmp: SQL SELECT bd.date AS uni_date , SUM (CASE WHEN wi.type_cd IN ('coll_30','coll_60') THEN 1 ELSE 0 END) AS wi_out_call_count , SUM (CASE WHEN wi.type_cd IN ('coll_90','coll_120') THEN 1 ELSE 0 END) AS wi_late_bp_nc_count , SUM (CASE WHEN wi.type_cd IN ('coll_90') THEN 1 ELSE 0 END) AS wi_late_call_count FROM work_items wi LEFT JOIN denorm_next_business_dates bd ON bd.date <= CAST(CASE WHEN wi.resolved_on IS NULL THEN current_date ELSE wi.resolved_on END AS DATE) JOIN loans l ON l.id = wi.loan_id WHERE wi.type_cd LIKE ('coll_%') AND (bd.date >= CAST(wi.created_on AS DATE)) AND (bd.date >= current_date - 14) AND (bd.date <= current_date) AND ((wi.created_on >= current_date - 14 AND wi.created_on <= current_date) OR (wi.created_on <= current_date AND wi.resolved_on IS NULL) OR (wi.resolved_on >= current_date - 14 AND wi.resolved_on <= current_date) ) AND l.gov_law_state_cd <> 'WA' AND ((wi.resolved_on IS NULL) OR (wi.resolved_on::TIME > '06:55:00')) GROUP BY bd.date ORDER BY bd.date ;
Really folks I'm at my wit's end here. I honestly don't think this can be done with QlikView's loads/join syntax. Doesn't help that it's almost impossible to get your code to format well either.
OK I'll try it out. Thanks for the help Nigel; your weekend is guaranteed to be better than mine.
Btw: thank you for letting me know that the Work_Items_Today is temporary. The documentation sure doesn't tell me that, and I'd have no reason to think that table would NOT stay around.
