Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
What I think you need here is Preceding Load.
Here is few blogs to read
Hi Sasidhar,
This syntax kinda works but it only brings back 1 record.
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
Hi Stefan,
Please could you post sample data with expected outcome?
Regards
Sasi
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;
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;
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;
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 .