# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

## 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

## 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

## 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?

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

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;