Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
BI Consultant
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
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.
BI Consultant