topic Re: SQL statements to QV loadscript in QlikView

SQL statements to QV loadscript

Tue, 18 Oct 2011 16:24:48 GMT

Hi,

I need to convert a big chunk of SQL statements into QV loadscript version. These SQl statemnts are feeding data from database but I want it to feed from QVD with all the group by and where conditions along with the aggregations still in place.

For example how do I convert this statement to QV version ??

sum(case when AL2.new_used_cde='N' and AL2.sold_to_customer_ind='Y' then 1

when AL2.new_used_cde in ('C','U') and AL2.retail_type_cde in ('B','C','76') then 1

else 0 end) as new_sales,

FROM dbo.fact_vehsal AL1

left join dbo.dim_vesalespro AL2 on AL2.a_key=AL1.a_key

left join dbo.dim_vemo AL3 on AL3.b=AL1.b

left join dbo.dim_d dlr on dlr.c=AL1.c

left join dbo.dim_d AL4 on AL4.c=dlr.d

WHERE

AL5.sales_year>=2010

AND AL4.country='USA'

and AL4.deal_no not in ('11111','11112','11113')

GROUP BY

AL4.dealer_no,

al3.vehicle_model_nme

What would be a good approach to start with? Please help. Any suggestion counts. I have attached a small copy of qvw.

Thanks,

Abha

Re: SQL statements to QV loadscript

Miguel_Angel_Baeyens — Tue, 18 Oct 2011 17:07:36 GMT

Hi Abha,

QlikView automatically links a table when one or more of their fields have the same name. Assuming that there are no other fields than the "link" fields renamed in the SELECTs below (otherwise QlikView will create several Syntehtic tables with the "composite key" of all possible values from fields with same name) the final script might look like this

TotalTmp:
LOAD *,
     a_key as LinkA1A2,
     b AS LinkA1A3,
     c AS LinkA1dlr;
SQL SELECT * FROM fact_vehsal;
LEFT JOIN LOAD a_key AS LinkA1A2,
     *;
SQL SELECT * FROM dim_vesalespro;
LEFT JOIN LOAD b AS LinkA1A3,
     *;
SQL SELECT * FROM dim_vemo;
LEFT JOIN LOAD c AS LinkA1dlr,
     d as LinkA4dlr
     *;
SQL SELECT * FROM dim_d;
// In your example dim_d is twice; on purpose?
LEFT JOIN LOAD c AS LinkA4dlr,
     *;
SQL SELECT * FROM dim_d
Final:
LOAD
     RangeSum(If((new_used_cde = 'N' AND sold_to_customer_ind = 'Y') OR Match(new_used_cde, 'C', 'U') AND Match(retail_type_cde, 'B', 'C', 76), 1, 0), Peek('new_sales')) AS new_sales,
     *
RESIDENT TotalTmp
WHERE sales_year >= 2010
AND country = 'USA'
AND Match(deal_no, 11111, 11112, 11113) = 0
GROUP BY dealer_no, vehicle_model_nme;
DROP TABLE TotalTmp;

Note that JOINing in QlikView may take a lot of time and memory (at least version 10 SR3 update 1) depending on the number of rows these tables have.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: SQL statements to QV loadscript

Tue, 18 Oct 2011 20:13:09 GMT

Hi Miguel,

Thank you so much for the reply. I did follow the same process and it was taking a long time but I didnot understand

TotalTmp: LOAD *, a_key as LinkA1A2, b AS LinkA1A3, c AS LinkA1dlr; SQL SELECT * FROM fact_vehsal;

why are these needed. I am feeding data from qvds. Can you please expain the process you were doing please.

Regards,

Abha

Re: SQL statements to QV loadscript

Miguel_Angel_Baeyens — Wed, 19 Oct 2011 07:15:39 GMT

Hi Abha,

Regardless your data source (in my example above, SQL, in your case, QVD files) you need to name fields alike to link or associate tables. In a SQL statement you say what field links to what field. That's why I am renaming fields in all tables (or QVD files) so they can be linked.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica