Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can any one explain with the help of suitable example me where and in which condition we have to use Group by and Order by in qlikview load script.
I have sample raw data in excel attached below.so on that raw data in which scenario we can apply group by and order by in load script.
Hi,
It seems something wrong with Attachment please can you attach again.
Thanks,
Mukram.
as Mohammed wrote, the attach is wrong.
with a order by load you can order your datas by fields, expample:
load
OrderNo,
CustomerNo,
OrderDate,
Value
from source
order by CustomerNo, OrderDate DESC;
this will order your datas
first by Customer No ascending,
second by Order Date descending
so you have the newest order date for each customer in the first line.
How to use:
For example in most ERP systems (like SAP) there is no daily order input value. you have a orderno with create date and edit date (the last edit date) but not for every edit date a separate line.
So you can store your order sets within a datefield today() and compare against the last entry.
load
OrderNo,
snaphot as Date,
if(OrderNo = previous(OrderNo),value-previous(value) , value) as value,
other fields.
If you have order your datas ba OrderNo and snapshot date descending you con compare that the order no in actual line is equal to order no in line above so the actual order value is actual - last entry = delta. Otherwise (if OrderNo unequal to previous OrderNo) there is a new order line).
group by you need within aggregaations in script.
example:
load
CustomerNo,
max(OrderDate) as LastOrderDate
resident source
group by CustomerNo;
this script wil create a table with CustomerNo and the max (the last) order date.
Hope this helps without your own datas.
Regards