
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
