Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jeevays7
Partner - Creator III
Partner - Creator III

Direct discovery with group by clause

Hi All,

I have a table in database like below:

  

IDFUNDComments
1100ABC
2100Test
3101Test2

Im fetching data from this table using DIRECT QUERY.

Now i want to show the data like below in table chart. the problem is i am not able to show the comments when it has two line for same fund. i need to show the max ID's Comments for each Fund.

Qlik Chart (Output)  :

FUNDComments
100Test
101Test2

How to do? please help.

Can i use Aggr in DIRECT QUERY? but group by is not working in the script using direct query.

6 Replies
petter
Partner - Champion III
Partner - Champion III

Have you tried using the Qlik FirstSortedValue() aggregation function in the chart? You can use the ID as weight parameter to get the the maximum ID's corresponding comment.

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/BasicAggregationFun...

There is also a version of FirstSortedValue() for scripts:

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/AggregationFunctions/fir...

jeevays7
Partner - Creator III
Partner - Creator III
Author

Hi Peter,

I have tried, but it is not working.

Direct query will support FirstSortedValue() function??

petter
Partner - Champion III
Partner - Champion III

Did you try it in your chart/table in a sheet?

jeevays7
Partner - Creator III
Partner - Creator III
Author

Hi Peter,

I did, but it is not working.

The expression is:

FirstSortedValue(Comments,-ID,1).

here, the ID and Comments fields are in measure of direct query.

petter
Partner - Champion III
Partner - Champion III

Well - I can't help you then. My advice to you is to look at other ways to integrate with your sources since Direct Discovery is in "maintenance" mode and hasn't been developed or will not be developed further. In light of the limitations of Direct Discover I think other approaches like ODAG and SSE or even frequently used reloads would be the best way for most if not all companies now. Big Data Indexer is something that will come in the Qlik Sense September release also in its first version.

jeevays7
Partner - Creator III
Partner - Creator III
Author

Ok. Thank you so much for your reply's peter.