How to convert this SQL statement to Qlikview syntax

[Anonymous]

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

[sasiparupudi1]

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

[Gabriel]

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/

[Anonymous]

Hi Sasidhar,

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

[Anonymous]

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

[sasiparupudi1]

Hi Stefan,

Please could you post sample data with expected outcome?

Regards

Sasi

[sasiparupudi1]

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;

[jonathandienst]

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;

[Kushal_Chawda]

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;

[Gabriel]

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 .