Group by and Order by

dhavalvyas — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Group by and Order by

mdmukramali — Wed, 20 Dec 2017 18:30:13 GMT

Hi,

It seems something wrong with Attachment please can you attach again.

Thanks,

Mukram.

Re: Group by and Order by

martinpohl — Wed, 20 Dec 2017 21:07:57 GMT

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

topic Group by and Order by in QlikView

Group by and Order by

Re: Group by and Order by

Re: Group by and Order by