Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
msawyercke
Not applicable

Question re: Sorting

Hi,

I'm using a pivot table and have a question about sorting.  Here is a screenshot of the table, where I'm sorting by Agmnt ID, Obligation Date and Unit Location:

Capture.JPG

As you can see the date "6/30/2020" has two obligations, one which has been 'Fufilled' (by "Ashland"0 and one which has not (and therefore is null). 

My question is, how can I get the records to sort so that "Ashland" is listed first, and the null field is listed second?

I thought it would be easy to just go into the sort panel for the "unit Location" field and sort alphabetically as shown below, but it does not affect the sort sequence:

Capture1.JPG

Thanks for any help or ideas anyone can offer! 

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Question re: Sorting

I think it is showing square brackets around the whole thing. Make sure to remove them..

Capture.PNG

18 Replies
sunny_talwar
Not applicable

Re: Question re: Sorting

What if you try like this

Dual([Unit Location], If(Len(Trim([Unit Location])) = 0, 0, 1))

and then sort numerically

msawyercke
Not applicable

Re: Question re: Sorting

HI Sunny,

Hope you are doing well and thanks for your reply.

Would I place that expression in my load script editor?

Thanks again!

sunny_talwar
Not applicable

Re: Question re: Sorting

You can do that or you can put this as a calculated dimension.

msawyercke
Not applicable

Re: Question re: Sorting

Sunny,

The Location field is actually 'Unit Location Dim', and when I use the following expression, it is not populating the field with the correct location information to match with the Agreement ID or fulfilled obligation:Capture.JPG

msawyercke
Not applicable

Re: Question re: Sorting

In case it is helpful, here is the expression in my script editor that defines Unit Location:

Dual("Unit Location", AutoNumber("Unit Location"&"Address Line 1")) as "Unit Location Dim",

sunny_talwar
Not applicable

Re: Question re: Sorting

Oh so it is already a Dual field. Have you tried sorting this numerically?

msawyercke
Not applicable

Re: Question re: Sorting

Yes, I have tried that.  As a matter of fact, I added in a numeric unique identifier (PS#), and it doesn't acknowledge the desirable sort even with that field in the table.

But, back to your question, I have tried sorting numerically on the 'Unit Location" field as follows, and do not get the desired result:

Capture.JPG

sunny_talwar
Not applicable

Re: Question re: Sorting

Is the dimension for this table coming from multiple tables? Would you be able to share your qvf to take a look at?

msawyercke
Not applicable

Re: Question re: Sorting

The dimension is from one other table, and I'm not sure how to share a qvf file...I'm apologize...