Get what was written in a search box

[#1]

This is my first question on QlikCommunity. Thanks a lot to everyone who will try to help me.

In my application, I have a list of sells and sellers. An excel file contains one line for each sell. Logically, there is only one seller for each sell.

But, sometimes, in the column "Seller", there are more than one name (separated either by a " " or a "," or a "|"...).

I would like to know the number of sells in which appears a seller selected (just the number).

That's why I think that wirting the name of the seller in a search box could get all the apparition of the seller in the list of "Seller".

Then, I would like to write the number after the name which was written in the search box, and only this.

Let show you an example :

Table of sells :

     Sale_id     Seller     ...

     1               A

     2               B

     3               A

     4               B|C

     5               D,A,B

     6               D

In the search box : "D"

What will be written in current selections "D,A,B" and "D"

What I would like to have : "Number of sales of D : 2"

And not, with a getCurrentSelection(Seller) : "Number of sales of D,A,B and D : 2"

Can anyone help me, or give me another solution ?

Thanks a lot

[Jason_Michaelides · Accepted solution]

You could use a variable and an input box plus a trigger to achieve this.  Or you could (and probably should) adjust the data model using SubField().

1st solution (variable)

• create a variable, vSeller
  
• add an input box and tie it to that variable
• In document Properties > Triggers, set an OnChange event on the variable vSeller - Select In Field
  • Field: Seller
  • Search String: ='*' & vSeller & '*'
• In a text box type: ='Number of sales for seller ' & vSeller & ' = ' & COUNT(Sale_id)

2nd solution (SubField())

In your load script you already have something like:

Sales:

LOAD

     *

FROM SalesData Excel...;

Now add...

Sellers:

LOAD

     Sale_id,

     SubField(Seller,' ')     AS     SingleSeller

RESIDENT Sales;

Now the delimited strings of Selelrs will be broken out into separates and you can just use SingleSeller instead.NOTE: Be aware of any expressions that count or sum Sale_id as they may have doubled up.

This also assumes a constant delimeter of ' ' which you don't have so some data cleanup or more scripting will be necessary.

Should get you on your way,

Hope it helps,

Jason

[#3]

Thanks a lot Jason !

As I said, your solution with "subfield()" is quite more complicated for me as the Excel file is completed by many shops which don't have the same rules to fill the column Seller. But I keep it in mind if I need it later.

So I try the other solution with the variable, and it worked very well.

So again thank you

[Jason_Michaelides]

If you know what all the delimeters are you could use MapSubString() to replace them all for a common one first.