Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
msawyercke
Creator
Creator

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

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

Capture.PNG

View solution in original post

18 Replies
sunny_talwar

What if you try like this

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

and then sort numerically

msawyercke
Creator
Creator
Author

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

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

msawyercke
Creator
Creator
Author

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
Creator
Creator
Author

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

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

msawyercke
Creator
Creator
Author

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

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

msawyercke
Creator
Creator
Author

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