QlikSense sort a dual() calculated dimension

[dselgo_eidex]

Hello, I am trying to make a pivot table in QlikSense where the dimension is =dual(Name, ID). I want the table to show the names in alphabetical order, but it doesn't seem to be doing that.

I've tried the following:

• Sort alphabetically
• Sort by Expression (I tried "=Name" and "=Only({1} Name)")

Any help would be appreciated.

[dselgo_eidex]

Unfortunately, this does not work either. In the sample app above I have a table that sorts the dual function by an expression and it still does not sort correctly.

[mikaelsc]

seems the expression must return a num value:

try this :

match(left(lower(Name),1),'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z')

[dselgo_eidex]

Unfortunately, this is only a half solution because it will only sort based on the first letter of their name. I understand if this is my only option, but I feel like there should be a better way.

[mikaelsc · Accepted solution]

(was already expecting this 🙂

add the "alphabetical value" in your script by sorting your table by name asc, and adding rowno() as "the alphabetical" order.

[dselgo_eidex]

Lol, you and I had the exact same thought. I'm reloading my load script as I'm typing this.

[mikaelsc]

Data:

LOAD * INLINE [

ID, Name, Measure1, Measure2

    1, 'Everdeen, Katniss', 1, 1

    2, 'Mellark, Peeta', 2, 2

    3, 'Everdeen, Prim', 3, 3

    4, 'Abernathy, Haymitch', 4, 4

    5, 'Hawthorne, Gale', 5, 5

    6, 'Trinket, Effie', 6, 6

    7, 'Crane, Seneca', 7, 7

    8, 'Everdeen, Katniss', 8, 8

    9, 'Mellark, Peeta', 9, 9

    10, 'Heavensbee, Plutarch', 10, 10

];

left join(Data)

Load

ID,

    Name,

    rowno() as AlphabeticalOrder

resident Data

order by Name asc;

[mikaelsc]

ok ok gg

[dselgo_eidex]

I actually created the Dual field in the script:

left join(Data)

Load

    ID,

    Name,

    dual(Name, rowno()) as Person

resident Data

order by Name asc;

This makes it easier because I won't have to sort via an expression.