Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

SQL statements to QV loadscript

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

Tags (3)
3 Replies
Highlighted

Re: SQL statements to QV loadscript

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

Highlighted
Not applicable

Re: SQL statements to QV loadscript

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

Highlighted

Re: SQL statements to QV loadscript

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