Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pawwy1415
Contributor II

Group by help

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Group by help

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

;

 

 


4 Replies
MVP
MVP

Re: Group by help

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

;

 

 


MVP
MVP

Re: Group by help

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
Contributor II

Re: Group by help

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
Honored Contributor III

Re: Group by help

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;

Community Browser