topic How to convert this SQL statement to Qlikview syntax in QlikView

How to convert this SQL statement to Qlikview syntax

Anonymous — Wed, 05 Aug 2015 07:57:31 GMT

Hi everyone.

I recently started with qlikview. Seeing as I dont have much experience yet, I find it difficult to convert SQL syntax to Qlikview syntax.

I have the following sql statement:

select product_no, sum(qty)

from fct_table

where sk_month = (select max (sk_month)

from fct_table)

and voucher_no in (select max(voucher_no)

from fct_table

where sk_month = (select max (sk_month)

from fct_table)

group by branch_no,product_no)

group by product_no;

Does anyone have an idea on how to convert this to qv syntax? I already have a csv of the fct_table, so I can just load that csv in.

Thanks and regards,

Stefan

Re: How to convert this SQL statement to Qlikview syntax

sasiparupudi1 — Wed, 05 Aug 2015 08:24:56 GMT

fct_table:

load //num(date(Date#(sk_month,'MM'),'MM')) as sk_month,product_no,qty,voucher_no,branch_no

sk_month,product_no,qty,voucher_no,branch_no

inline

[

sk_month,product_no,qty,voucher_no,branch_no

1,100,20,1234,1

2,220,30,1235,1

3,300,33,1236,2

4,120,44,1237,2

5,400,3,1238,3

];

MaxMonth:

load Max(sk_month) as sk_monthMax Resident fct_table;

let vMaxMonth=peek('sk_monthMax',0,'MaxMonth');

MaxVoucherNo:

load max(voucher_no) as voucher_noMax

Resident fct_table

where sk_month = '$(vMaxMonth)'

group by branch_no,product_no;

let vMaxvoucher_no=peek('voucher_noMax',0,'MaxVoucherNo');

drop Table MaxMonth,MaxVoucherNo;

NoConcatenate

final:

load product_no, sum(qty) as TotalQty

Resident fct_table

where sk_month ='$(vMaxMonth)' and voucher_no='$(vMaxvoucher_no)'

group by product_no;

drop Table fct_table;

HTH

Sasi

Re: How to convert this SQL statement to Qlikview syntax

Gabriel — Wed, 05 Aug 2015 08:39:51 GMT

Hi,

What I think you need here is Preceding Load.

Here is few blogs to read

Preceding Load

http://www.quickintelligence.co.uk/preceding-load-qlikview/

Re: How to convert this SQL statement to Qlikview syntax

Anonymous — Wed, 05 Aug 2015 09:17:55 GMT

Hi Sasidhar,

This syntax kinda works but it only brings back 1 record.

Re: How to convert this SQL statement to Qlikview syntax

Anonymous — Wed, 05 Aug 2015 09:18:50 GMT

Hi Gabriel.

We extract our tables to csv's on our QV server and then read in those csv's.

We dont use sql queries in the back-end of our apps

Re: How to convert this SQL statement to Qlikview syntax

sasiparupudi1 — Wed, 05 Aug 2015 09:26:26 GMT

Hi Stefan,

Please could you post sample data with expected outcome?

Regards

Sasi

Re: How to convert this SQL statement to Qlikview syntax

sasiparupudi1 — Wed, 05 Aug 2015 09:34:06 GMT

Hi Stefan

may be try this

fct_table:

load

sk_month,product_no,qty,voucher_no,branch_no

inline

[

sk_month,product_no,qty,voucher_no,branch_no

1,100,20,1234,1

1,100,20,1239,1

2,220,30,1235,1

3,300,33,1236,2

4,120,44,1237,2

5,400,3,1238,3

5,500,3,2238,3

5,400,3,2338,3

];

MaxMonth:

load Max(sk_month) as sk_monthMax Resident fct_table;

let vMaxMonth=peek('sk_monthMax',0,'MaxMonth');

drop Table MaxMonth;

left join

load product_no,max(voucher_no) as voucher_noMax

Resident fct_table

where sk_month = '$(vMaxMonth)'

group by branch_no,product_no;

NoConcatenate

final:

load product_no, sum(qty) as TotalQty

Resident fct_table

where sk_month ='$(vMaxMonth)' and voucher_no=voucher_noMax

group by product_no;

Re: How to convert this SQL statement to Qlikview syntax

jonathandienst — Wed, 05 Aug 2015 09:42:07 GMT

I think the middle part of your code should read

left join (fct_table)

load product_no,max(voucher_no) as voucher_noMax

Resident fct_table

where sk_month = '$(vMaxMonth)'

group by branch_no,product_no;

Re: How to convert this SQL statement to Qlikview syntax

Kushal_Chawda — Wed, 05 Aug 2015 09:42:24 GMT

try this

Data:

LOAD branch_no,

product_no,

max(Sk_month) as Sk_Month

From Fact.qvd(qvd)

group by branch_no,product_no;

left join

load max(voucher_no) as voucher_no,

Sk_Month

From Fact.qvd(qvd)

Group by Sk_Month;

Product:

LOAD product_no,

Qty,

Sk_Month

From Fact.qvd(qvd);

Inner Join

LOAD Distinct max(Sk_Month) as Sk_Month

Resident Data;

DROP Table Data;

Final:

LOAD product_no, sum(Qty) as Qty

Resident Product

group by product_no;

DROP Table Product;

Re: How to convert this SQL statement to Qlikview syntax

Gabriel — Wed, 05 Aug 2015 09:46:00 GMT

Hi,

If I understand you correctly, you use QlikView to extract data and save (store) into csv then use QlikView to read the csv back into QlikView.

If my assumption is correct, why doing that?

Why not save (Store) your data extracted into .qvd which is quicker for QlikView load into QlikView .