topic Re: convert SQL query in QlikView

convert SQL query

mrbqlik2009 — Sat, 19 May 2018 09:14:48 GMT

Hi,

I have sql query, how can write it in qlik view script.

SELECT ItemTitle, CuName, Qty, SumAllByProduct,SumAllByProductTopOne

from

(

select ItemTitle, CuName, sum(Quantity) Qty

,(select sum(Quantity) From dbo.nvoice where ItemTitle=S.ItemTitle) SumAllByProduct

,(select sum(QtySum)

From( select top 10 ItemTitle, CuName, sum(Quantity) QtySum

from dbo.nvoice

where ItemTitle=S.ItemTitle

group by ItemTitle, CuName

order by qtySum DESC)HH) SumAllByProductTopOne

From dbo.nvoice S

group by ItemTitle, CuName

) as m

Re: convert SQL query

petter — Sat, 19 May 2018 14:50:22 GMT

Is there a good reason why you want to convert the SQL into a load script query?

I mean you can send this SQL as is via the ODBC or OLEDB driver like this in your load script:

LOAD

SQL

SELECT ItemTitle, CuName, Qty, SumAllByProduct,SumAllByProductTopOne

from

(

select ItemTitle, CuName, sum(Quantity) Qty

,(select sum(Quantity) From dbo.nvoice where ItemTitle=S.ItemTitle) SumAllByProduct

,(select sum(QtySum)

From( select top 10 ItemTitle, CuName, sum(Quantity) QtySum

from dbo.nvoice

where ItemTitle=S.ItemTitle

group by ItemTitle, CuName

order by qtySum DESC)HH) SumAllByProductTopOne

From dbo.nvoice S

group by ItemTitle, CuName

) as m

;

Of course it is possible to rewrite all of it to pure Qlik Load Script but is that really a needed here?

Re: convert SQL query

mrbqlik2009 — Sat, 19 May 2018 16:31:58 GMT

Hi,

dbo.nvoice have 1000000 records.

I load dbo.nvoice in my qlikview one time.

when i want run this sql query, and read from sql server it take a long time.

I want calculate Qty, SumAllByProduct,SumAllByProductTopOne in qlikview.

Re: convert SQL query

vishsaggi — Sat, 19 May 2018 21:56:05 GMT

I would go QVD approach in this regards as you have more rows. I mean you can load all the rows without any aggregations. Load your data into QVD as extract. Then load into QV from QVD then do the transformation. Something likle ELT process.

Re: convert SQL query

petter — Sun, 20 May 2018 11:13:36 GMT

Blindly converting a SQL query into something that works in a load script is seldom a good idea.... so that's why I ask this question:

What does the fourth and fifth column in the result set from this SQL query give?

The fourth seems to be straightforward as the total sum for each product (TitleName).

The fifth is not so easy to understand. It doesn't make sense to me. Could you explain in plain english what you are trying to calculate here?

Re: convert SQL query

mrbqlik2009 — Sun, 20 May 2018 11:33:20 GMT

Let me say you my problem clearly.

I had a table in SQL server with about 1,000,000 records.

I loaded this table records in QlikView, there is no problem.

Problem start from time that i wanted calculate some expression. I couldn't calculate that in Qlik. Because that i have to write query in SQL, and load it in Qlik. This load process take long time.

My question is how to calculate this columns in Qlik.

I create sample data inline in Qlik that I send here.

My question is calculate expressions that i put in chart,