Use of Dual function in script with 'else catch all'

[reddwarfcrew]

Hi, I'm using the dual function in my scripting to force a custom sort in my charts.  It works great except I have a 'catch other' at the end.  Let me explain. My script is along the lines of:

if([Team]="Steve",dual("Steve",1),
if([Team]="Tom",dual("Tom",2),
if([Team]="Fred",dual("Fred",3),
if([Team]="Harry",dual("Harry",4),
dual([Team],5)))))

So basically if the team is none of the above, then put at the end.  This works great until you have more than 1 non-declared team.  So lets say we have 10 records assigned to the Team John and a further 5 to the Team Sam, Qlik will nicely sort the first 4 teams as declared, but then assigns the last 15 records to John instead of John and Sam.

Now I sort of guess that it is saying put everything else into 5th place in the sort and if you have more than one extra team then it can't do that as one has to be 5th and one 6th, but this seems odd for Qlik to effectively corrupt the data.

So is there a way around this, eg can I replace '5' with an autonumber starting at 5???  I appreciate I could end the statement with: dual("Other",5), but I still want to see all remaining teams split out, just order to the bottom.

Any help appreciated.  Hope it makes sense.

Thanks.

[Anil_Babu_Samineni]

Dual function works until we declare the statement, for your case if you want to use what ever not declared, we can store them into others using below
if([Team]="Steve",dual("Steve",1),
if([Team]="Tom",dual("Tom",2),
if([Team]="Fred",dual("Fred",3),
if([Team]="Harry",dual("Harry",4),
dual('others',5)))))

Here, synthatically I can see we can deliver correct sort using inline table for that and mapping info Team field. Then use inline field in sort option to get that done for workaround.

[jonathandienst]

You can't have multiple text representations for one numerical value (which is the intrinsic value of the dual), so you can use the above , or something like this:

LOAD DISTINCT 
 Team,
 if([Team]='Steve',dual('Steve',1),
 if([Team]='Tom',dual('Tom',2),
 if([Team]='Fred',dual('Fred',3),
 if([Team]='Harry',dual('Harry',4),
 dual(Team, RowNo() + 4))))) as DTeam,
 ...
Resident ...
ORDER BY Team;	// this line optional