topic Translating SQL to QlikView joins in QlikView

Translating SQL to QlikView joins

Fri, 23 Oct 2009 22:13:51 GMT

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.

Translating SQL to QlikView joins

Fri, 23 Oct 2009 22:58:47 GMT

I am unsure as to why you want to convert this to QlikView joins, why don't you simply execute a LOAD from the SQL statement?

Maybe I'm missing something.

Translating SQL to QlikView joins

Fri, 23 Oct 2009 23:01:18 GMT

Nigel: ?You mean you can rig a SQL statement to use a qvw file instead of a typical table designation? ?How does one do that?

The point is that I won't be reading SOLELY from the SQL table Work_Items anymore. I'll be reading from both a qvw file as well as yesterday's Work_Item records from the database.

I know *I*'m missing something, I just don't know what...

Translating SQL to QlikView joins

Fri, 23 Oct 2009 23:07:15 GMT

No, unfortunately not, what I mean is you can create a QVW table direct from a SQL statement by something like:

QVW_TableName:

LOAD Customer,

SalesValue;

SQL SELECT Customer,

SUM(Sales)

FROM dbo.vw_sales

GROUP BY Customer


This is just an example, but it shows the principle of creating a table in the qvw load script, I am not sure what you need to do that is different to this.
Regards,</body>

Translating SQL to QlikView joins

Fri, 23 Oct 2009 23:08:18 GMT

Sorry, just saw your next reply.

I think you need to look at creating QVD files inside your load script, then you can concatenate the SQL data to your QVD file.

Translating SQL to QlikView joins

Fri, 23 Oct 2009 23:12:00 GMT

I get the impression that you want to load todays data and join that to an historical table that holds the remainder of the data, is this correct.

Translating SQL to QlikView joins

Fri, 23 Oct 2009 23:14:09 GMT

Ah, yes sir, that's what, I believe, the documentation would call "Loading data from succeeding input table". Sadly, you can't refer to historical qvw data in that context as the SQL joins to QV resident tables or raw files always give you a syntax error.

Translating SQL to QlikView joins

Fri, 23 Oct 2009 23:16:51 GMT

Indeed it is, and oddly enough, THAT'S already been done and confirmed LOL. I generate a resident table which can be used for subsequent QlikView loads/joins/whatevers. This is the resident Work_Items table that I "spot" you in the above challenge. Sadly that resident table is totally useless to a SQL SELECT statement...

Translating SQL to QlikView joins

Fri, 23 Oct 2009 23:23:54 GMT

OK, so here goes with a suggestion, this will be the last entry of the day for me because I'm off home (thank god).

Your first load creates a resident table (Work_Items), in your following SQL statement you should have something like:

Work_Items_Today:
Concatenate (Work_Items)
LOAD A,B,C;
SQL Select A,B,C FROM TABLENAME;

This is a method I use all the time to join two tables together.

Hope it helps, but if not, have a great weekend and we'll work through something next week.

Translating SQL to QlikView joins

Fri, 23 Oct 2009 23:24:56 GMT

Sorry, just as a note......

Your QlikView document will then only have Work_Items available as a table, the table Work_Items_Today will not exist as its just temporary.

Translating SQL to QlikView joins

Fri, 23 Oct 2009 23:27:39 GMT

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.