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

[loganathan]

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

[SerhanKaraer · Accepted solution]

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/AggregationFunc...

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.

[loganathan]

Hi Yes its works. Thank you so much