How to use Count & Group by in Qlikview?

[#1]

Hello Gurus,

I'm still a newbie in Qlikview and I need your help to know how to count occurences within a set of records with something similar to a Group by would do in SQL.

I want to count the number of delivery per order:

Here is how my simplified order table:

Order	Delivery
1794645	83202732
1794645	83193750
1794645	83189667
1794645	83187587
1794645
1795925	83203478
1795925	83201660
1795925	83194195
1795925	83193519
1795925	83192433
1795925	83190958
1795925
EDI0175948	83268850
EDI0175948	83248954
EDI0175948	83211740
EDI0175948	83209152
EDI0175948	83205733
EDI0175948
WEB0588425	83251071
WEB0588425	83200037
WEB0588425	83195718
WEB0588425
EDI0176140	83195951
EDI0176140

As it's basically like if I was doing a pivot table with Order ID in dimension and expression =count(DISTINCT Delivery) I figured that I could use the Aggr() function but it doesn't work, not sure that I understood it properly ...

Thanks in advance for your help & guidance.

With Regards

Ismael

[MK_QSL]

Create a Flag in your Load Script as below

Data:

Load

  Order,

  If(Len(Trim(Delivery))=0 or IsNull(Delivery),0,1) as Flag,

  Delivery

Inline

[

  Order, Delivery

  1794645, 83202732

  1794645, 83193750

  1794645, 83189667

  1794645, 83187587

  1794645,

  1795925, 83203478

  1795925, 83201660

  1795925, 83194195

  1795925, 83193519

  1795925, 83192433

  1795925, 83190958

  1795925,

  EDI0175948, 83268850

  EDI0175948, 83248954

  EDI0175948, 83211740

  EDI0175948, 83209152

  EDI0175948, 83205733

  EDI0175948,

  WEB0588425, 83251071

  WEB0588425, 83200037

  WEB0588425, 83195718

  WEB0588425,

  EDI0176140, 83195951

  EDI0176140,

];

Now use Straight or Pivot Table with Order as Dimension and expression as

=SUM(Flag)

UPDATE:

Another way to achieve the same result in script as below

Data:

Load

  Order,

  If(Len(Trim(Delivery))=0 or IsNull(Delivery),0,1) as Flag,

  Delivery

Inline

[

  Order, Delivery

  1794645, 83202732

  1794645, 83193750

  1794645, 83189667

  1794645, 83187587

  1794645,

  1795925, 83203478

  1795925, 83201660

  1795925, 83194195

  1795925, 83193519

  1795925, 83192433

  1795925, 83190958

  1795925,

  EDI0175948, 83268850

  EDI0175948, 83248954

  EDI0175948, 83211740

  EDI0175948, 83209152

  EDI0175948, 83205733

  EDI0175948,

  WEB0588425, 83251071

  WEB0588425, 83200037

  WEB0588425, 83195718

  WEB0588425,

  EDI0176140, 83195951

  EDI0176140,

];

Final:

Load

  Order,

  SUM(Flag) as TotalDeliveryPerOrder

Resident Data

Group By Order;

[rustyfishbones]

Something like

Would give you the Order with the most deliveries and return the count of Deliveries

[#4 · Accepted solution]

Hi folks,

With a last try it seems that Aggregation function Aggr() was the one:

=Aggr(count(DISTINCT Delivery),Order)

I was just mistyping it

Thank you all for your precious help.

See u

[#5]

Thx Manish but I wanted to avoid changing the script

[rustyfishbones]

Or you could use Set Analysis to get the count for a specific Order

like

=COUNT({<Order = {'1794645'}>} DISTINCT Delivery)

[MK_QSL]

Why you want to use Aggr ?

You can get the same result by using below...

COUNT({<Delivery = {"=Len(Trim(Delivery))>0"}>}DISTINCT Delivery)

or

COUNT(Distinct Delivery)

or

Count({<Delivery = {"=Not IsNull(Delivery)"}>}Delivery)

[#8]

Well I wanted to count Delivery per Order and then report an min, max & average by Sales office.

I found Aggr() function very convenient for this requirement considering that I don't want to change application script.

Thanks !

[MK_QSL]

Got it now.... Wondering because Min, Max and Avg requirements were never mentioned....

You have to use Aggr in those cases if you don't want to achieve result in Script/Back end...

Min(TOTAL Aggr(COUNT({<Delivery = {"=Len(Trim(Delivery))>0"}>}DISTINCT Delivery),Order))

Max(TOTAL Aggr(COUNT({<Delivery = {"=Len(Trim(Delivery))>0"}>}DISTINCT Delivery),Order))