topic Concat distinct with sorting in QlikView

Concat distinct with sorting

YoussefBelloum — Wed, 21 Apr 2021 12:02:23 GMT

Hi guys,

I need to get field (INDIC_3) values on textboxes, with the same sorting as the loading sort, using concat and then subfield.

here is what I get:

the green color represent the loading sort wich is correct, and the red color represent what I get with Concat(Distinct INDIC_3)

I tried to read all the threads about the same issue, so I tried using the third parameter of the Concat() with RowNo on the script and on the front end. nothing is working for me

PFA the sample data I'm using for this example

Thank you for your help

Re: Concat distinct with sorting

edwin — Wed, 21 Apr 2021 18:52:01 GMT

concat function does have sorting option:

Concat - script function ‒ QlikView

Re: Concat distinct with sorting

MayilVahanan — Thu, 22 Apr 2021 05:59:08 GMT

HI @YoussefBelloum

Try like below

LOAD INDIC_NIV_0,
INDIC_1,
INDIC_2,
INDIC_3,
INDIC_N2,
AutoNumber(INDIC_3) as INDIC_3No
FROM
[D:\Qlik\Com\test.xlsx]
(ooxml, embedded labels, table is Feuil1);

Front end: Text box:

=Concat(DISTINCT INDIC_3, ',', INDIC_3No)

Re: Concat distinct with sorting

Frank_Hartmann — Thu, 22 Apr 2021 12:19:31 GMT

if you want to do it without changing the script you could do as following:

=concat(distinct INDIC_3,',',match(ord(left(subfield(INDIC_3,' ',3),1))-64,'1','33','5','13'))

Re: Concat distinct with sorting

YoussefBelloum — Thu, 22 Apr 2021 12:57:43 GMT

@MayilVahanan thank you ! it works