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
and AL4.deal_no not in ('11111','11112','11113')
What would be a good approach to start with? Please help. Any suggestion counts. I have attached a small copy of qvw.
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
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
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,
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.
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.