Group by help

[pawwy1415]

Hi Experts,

Can any one please help me on below requirement.

I have a oracle query with Group by, Having and Order by clause like below.

In Qlik we are working with QVD's to build the model by taking the logic from above oracle query.

Up to Group by able to run in Qlik by replicating all logics into Qlik but struck after having clause.

Please help me how to replicate the logic into Qlik from having to order by.

Thanks in advance.

[petter · Accepted solution]

You would have to have a preceding LOAD statement and this LOAD could have a WHERE acting just like a HAVING clause that refers to a field that has a new name and could be an aggregate like max().

LOAD
  *
WHERE
  Not(IsNull( [Type of limit] )); // this will refer to the newly calculated max(....)
LOAD
  .....
  max([Type of limit]) as [Type of limit],
  .....
order by Desk_Name,
  Desk_ID,
  Desk_Description,
  .....
;

Here is a sample fully working test-script:

DATA:
LOAD
  Chr( Floor(Rand()*16)+Ord('A') ) AS C1,
  Chr( Floor(Rand()*26)+Ord('A') ) AS C2,
  If(Rand()<0.01,Null(),Ceil( Rand()*1000) + 1000) AS N
AUTOGENERATE
  10000;

LOAD
  C1,
  MinC2,
  SumN
WHERE Not(IsNull(SumN))   // This will work as the HAVING clause
  ;
LOAD
  C1,
  MinString(C2) AS MinC2,
  If(Rand()<0.1,Null(),Sum(N)) AS SumN    // Let approximately 10% be NULLS
RESIDENT
  DATA
GROUP BY
  C1
ORDER BY
  C1 DESC 
;

[petter]

You have to look at your data in the table when you comment out the red. Does the column [Type of limit] really contain any non-null values?

[keerthika]

Hi,

     Does your METRICNAME field has more than 15 characters in length

mid(METRICNAME,15)  this may cause the issue if length is less than 15.

keerthika

[sasiparupudi1]

May be try a resident load

Groupby:

Load

      DESKNAME,

      DATASECURITYID, 

      DESKDESCRIPTION,

      "Unit of Measure",

      Desk_Currency_Reported,

      groupingMetric,

      isStopLoss,

    ASOFDATE ,                                        

      max([Type of limit])                                      as [Type of limit],

      max([Limit Size])/1000                                    as [Limit Size],

      round(sum([Value of Usage])/1000, 2)                      as [Value of Usage],

      Round(fabs(if(isStopLoss = 'Y', RangeMin(Sum([Limit Usage]),0),Sum([Limit Usage]))),0.01) as [Limit Usage]

Resident MainTable_R1

      Group by DESKNAME,

      DATASECURITYID,

      DESKDESCRIPTION,

      "Unit of Measure",

      Desk_Currency_Reported,

      groupingMetric,

      isStopLoss,

    ASOFDATE

  order by DESKNAME,

      DATASECURITYID,

      DESKDESCRIPTION,

      Desk_Currency_Reported,

      [Type of limit]

    ;

  Drop table MainTable;

Final:

NoConcatenate Load

*

Resident Groupby

When Len(Trim([Type of limit])>0

;

Drop Table Groupby;