topic Re: How to sort this? in QlikView

How to sort this?

alexpanjhc — Wed, 25 Nov 2020 16:16:04 GMT

I have a sorting issue. Here it is

I have currently 7 unique numbers as following (I actually have 90 unique numbers)

ID	Number
1	20121
2	20121,20814,32105
3	43076,20126,55566
4	20814

all the unique numbers are :20121,20126,20814,32106,43076,55566

for each row, I want it the first position is always 20121, the second position is always 20126, and so on. If the row does not have this number, I will use 0 to fill it. so it will look like this.

ID	Number
1	20121,0,0,0,0,0
2	20121,0,20814,32105,0
3	0,20126,0,0,43076,55566
4	0,0,20814,0,0,0

I'd be highly appreciated if anyone please shed some lights,

I am not sure if this can be done easily in excel?

Re: How to sort this?

Anil_Babu_Samineni — Thu, 23 Mar 2017 01:59:21 GMT

How you are doing in excel?

Re: How to sort this?

Thu, 23 Mar 2017 03:32:44 GMT

Could you pls share ur application once.

Re: How to sort this?

Frank_Hartmann — Thu, 23 Mar 2017 10:19:13 GMT

see attached sample!

hope this helps

Re: How to sort this?

ahaahaaha — Thu, 23 Mar 2017 11:06:17 GMT

Hi Alex,

Maybe so, as variant (attached files).

Result

The number of unique values in the script is determined automatically. All comments in the script code.

Regards,

Andrey

Re: How to sort this?

alexpanjhc — Thu, 23 Mar 2017 19:58:12 GMT

Hi Andrey

This is very very helpful.

Can you explain what is your first step doing using while?

I know itno() will increase by 1 each time you execute it. How did the substringcount(number,',') effect in here?

This is a great solution and I just need to understand how it worked.

Thanks!