topic To get data sets only min(AlertCode) for each distinct 'CategoryCode' in App Development

To get data sets only min(AlertCode) for each distinct 'CategoryCode'

loganathan — Tue, 10 Jan 2023 14:14:36 GMT

Hi All,

In below sample i have 2 categoryCode '132335' and '452454' when i am loading the data i have to load distinct 'CategoryCode' with min(AlertCode) with its detail. Please advise

CategoryCode	AlertCode	col1	col2	col3
132335	2154216	red	a1	3eag
452454	2154218	brown	f	aer
452454	2154215	yellow	we	aewr
132335	2154219	green	ry	af
452454	2154220	black	v	afg
452454	2154212	black	fddhf	ag
132335	2154217	blue	tye	sdg
452454	2154214	brown	rt	shgf
132335	2154213	yellow	a1	xbxc
452454	2154221	green	d	xbxc

Expected result from the above table is:

CategoryCode	AlertCode	col1	col2	col3
452454	2154212	black	fddhf	ag
132335	2154213	yellow	a1	xbxc

TIA

Re: To get data sets only min(AlertCode) for each distinct 'CategoryCode'

SerhanKaraer — Tue, 10 Jan 2023 19:44:24 GMT

hello loganathan,

firstsortedvalue does the job.

You can check it out on help site:

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

load CategoryCode, FirstSortedValue(AlertCode, AlertCode) as AlertCode, FirstSortedValue(col1, AlertCode) as col1, FirstSortedValue(col2, AlertCode) as col2, FirstSortedValue(col3, AlertCode) as col3 group by CategoryCode; load * inline [ CategoryCode AlertCode col1 col2 col3 132335 2154216 red a1 3eag 452454 2154218 brown f aer 452454 2154215 yellow we aewr 132335 2154219 green ry af 452454 2154220 black v afg 452454 2154212 black fddhf ag 132335 2154217 blue tye sdg 452454 2154214 brown rt shgf 132335 2154213 yellow a1 xbxc 452454 2154221 green d xbxc ] (delimiter is '\t');

i hope it solves your problem.

Re: To get data sets only min(AlertCode) for each distinct 'CategoryCode'

loganathan — Wed, 11 Jan 2023 18:16:09 GMT

Hi Yes its works. Thank you so much