convert SQL query

[mrbqlik2009]

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

[petter]

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:

m:

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?

[mrbqlik2009]

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.

[vishsaggi]

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. 

[petter]

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?

[mrbqlik2009]

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,