topic Re: Sort Expression in QlikView

Sort Expression

Mon, 08 Oct 2012 19:59:14 GMT

Here is a challenge for you. I have an input box that allows the user to paste a column of Shipment ID Numbers from an Excel file, click 'Search' and get the required results. If one or more Shipment IDs are not found, then a text box pops up to let the user know which value was not found in the data. This all works great. However, now I have run into a problem that I can't seem to solve.

Our end users have requested that the report be sorted in the order entered in the search box. I am betting that there is a way to do this using the Sort expression, but I cannot get it to work for the life of me. If anyone can figure it out I will be both grateful and impressed. I have attached a sample file and an Excel document with an example of a search request. The end report will need to match this order.

Thanks!!

Re: Sort Expression

swuehl — Mon, 08 Oct 2012 20:41:35 GMT

Try this:

Go to sort tab on the chart properties and enter as sort expression for your ID field:

=match(ShipmentNumber,$(=replace(vSearch,' ',',')))

Re: Sort Expression

Mon, 08 Oct 2012 20:44:12 GMT

Thank you!! You are a genius, as always.

Re: Sort Expression

Tue, 09 Oct 2012 14:56:55 GMT

Quick question: The match expression is working perfectly in my test file, but isn't having any effect in my actual file.

The Search variable is vProNum and the field is FBProNum.

The Match expression for the sort is:

=match(FBProNum,$(=replace(vProNum,' ',',')))

Am I missing something obvious?

Re: Sort Expression

swuehl — Tue, 09 Oct 2012 15:11:19 GMT

The dollar sign expansion should not be different from the replace used in your search expression used with the search button action. I just noticed that you are using an additional trim() in your search expression:

=match(FBProNum,$(=replace(trim(vProNum),' ',',')))

But if your search works ok, this should also work. The essential idea is to expand to something like

=match(FBProNum, 679521,123431,987123,123432,123430)

(using the sample data). If your delimiter of your values you paste in the input box is different from ' ' (two spaces), you must adapt to something more appropriate.

You can actually check the outcome of the dollar sign expansion by pasting it to a text object.

Hope this helps,

Stefan

Re: Sort Expression

zagzebski — Fri, 09 Nov 2012 14:13:19 GMT

I came across this discussion and it is just what I was looking for. I want to paste several values into a search box. however (from your example file) when I paste a bunch of shipping ID into the search box nothing happens. Only when I did a single shipment ID.

It looks from your statement above "I have an input box that allows the user to paste a column of Shipment ID Numbers from an Excel file, click 'Search' and get the required results. " that you can do multiple at one time?

Re: Sort Expression

Fri, 09 Nov 2012 14:22:30 GMT

Yes, it is built to be able to enter multiple values at once. Although your requirements may vary, if you select a column on shipment IDs, paste them into the search box, click in the grey area, and then click 'Search' it will work. See below:

Copy values in Excel column:

Paste in to Input Box, then click outside the box:

Click Search:

In the example above I disabled all the conditions so that you could see all fields.

Re: Sort Expression

swuehl — Fri, 09 Nov 2012 15:07:39 GMT

AFAIR, the coded action was a little specific to the format of the pasted content (look how I replaced multiple spaces with a comma).

So, it's not really a generic copy&paste solution, i.e. if you're input format differs, the action might not work as expected.

Re: Sort Expression

zagzebski — Thu, 25 Apr 2013 12:53:03 GMT

This looks like it would be a great option but it I can't get it to work like your pictures above. I am sure I am missing a few steps but I added a new search object, then tried to paste my values into it...it didn't recognize the "paste" at all.

Can you provide the details from adding a search object through the pasting of the values into the object?

Thanks.

Steve

Re: Sort Expression

swuehl — Thu, 25 Apr 2013 13:03:05 GMT

Steve,

please have a look at the attached sample qvw. I remember there is also a preceding thread to this one that might explain some stuff a little better.

As first step, we don't actually use the search object, but an input field and store the pasted in values in 1 variable vSearch.

Then we've created a button with a select in field action that does the search for us:

='('&replace(trim(vSearch),' ','|')&')'

This search action is somewhat specific to the input format of the pasted Excel file values.

Hope this helps,

Stefan