Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
JAN. 24, Q&A with Qlik: Making the Move to Qlik Cloud Reporting: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
reddwarfcrew
Contributor II
Contributor II

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

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.

 

 

 

 

Labels (1)
2 Replies
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.
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner - Champion III
Partner - Champion III

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

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein