topic Re: Direct discovery with group by clause in App Development

Direct discovery with group by clause

jeevays7 — Wed, 22 Aug 2018 08:13:38 GMT

Hi All,

I have a table in database like below:

ID	FUND	Comments
1	100	ABC
2	100	Test
3	101	Test2

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) :

FUND	Comments
100	Test
101	Test2

How to do? please help.

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

Re: Direct discovery with group by clause

petter — Wed, 22 Aug 2018 11:20:41 GMT

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/BasicAggregationFunctions/firstsortedva…

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

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

Re: Direct discovery with group by clause

jeevays7 — Wed, 22 Aug 2018 11:39:16 GMT

Hi Peter,

I have tried, but it is not working.

Direct query will support FirstSortedValue() function??

Re: Direct discovery with group by clause

petter — Wed, 22 Aug 2018 12:09:55 GMT

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

Re: Direct discovery with group by clause

jeevays7 — Wed, 22 Aug 2018 12:19:10 GMT

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.

Re: Direct discovery with group by clause

petter — Wed, 22 Aug 2018 13:55:57 GMT

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.

Re: Direct discovery with group by clause

jeevays7 — Wed, 22 Aug 2018 14:09:15 GMT

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