Qlik Community

New to Qlik Sense

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

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Enriquemmesteo
Creator
Creator

Order table a certain way

Hello, the situation is as follows:

A friend wants to display a table visualization ordered by an auxiliary field called "Order", which he only needs to order this table correctly but nothing else. So he wants the table to be ordered by "Order", but without "Order" appearing on the table. 

In the sorting options of the table visualization, after removing "Order", seems like it doesn't work in the expression for ordering other dimensions. A simple solution was to put add "Order" as a measure, call it "." and set the color of the text to white for that colum, so its mostly hidden.

I wanted to order the data based on this "Order" directly from the script but it doesn't seem to work. The script looks something like this:

Load

Rowno() as Order,

A,

B,

C

from [1.xlsx](ooxml, embedded labels, table is Mats);

Load 

A,

B,C......//many fields

Z

from [2.xlsx](ooxml, embedded labels, table is Places);

 

Now I tried the following but for some reason when I display the fields of the table in a visualization, they are not ordered they way they should:

pre:

Load

Rowno() as Order,

A,

B,

C

from [1.xlsx](ooxml, embedded labels, table is Mats);

final:

NoConcatenate Load*Resident pre Order By Order asc;

Drop Table pre;

Load 

A,

B,C......//many fields

Z

from [2.xlsx](ooxml, embedded labels, table is Places);

 

Tried adding noconcatenate to the second excel load, including it in pre and adding the final: load*resident.... at the end but doesn't sort the data by ascending "Order" when "Order" isn't in the table visualization.

Any clues about what might be happening?

 

Labels (3)
2 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Your approach - even without rowno() - should work if you load this table before any of the included fields are loaded elsewhere.

Depending on your data and requirements it may be helpful to keep rowno/recno/autonumber as extra fields or to include them in some fields, like: dual([String-ID], autonumber([String-ID]))

- Marcus

Enriquemmesteo
Creator
Creator
Author

Thanks for your reply Marcus. I noticed something, lets see if you can help me out.

I noticed it is kinda working, as you mentioned...but not quite the way I am aiming for.

Lets say we have the 4 fields A,B,C,D and "Order"(which is rowno()).

We want a table visualization with columns A,B,C,and D. If we added the column "Order", it should have the values 1,2,3....to the last...or viceversa, for all the values of A,B,C and D. This is the order we want in the table without the addition of that column.

With the current approach it seems like, when we create the table visualization with A,B,C,D. It always tend to group by one of the A,B,C,D columns. So it will show first all the same values of A, which could have Order 1,15,22,178,654.... It is ordered, but not primarly on the "Order" we want.

Hope I didnt explain it too bad!