Club values in Filter Pane and Applymap

[#1]

All,

I am a new QlikSense user. I am encountering a small challenge to club data in the filter pane and applying mapping so the dashboard should work when the user select clubbed data.

I have a filter pane with company names as dimension, so I want to club together values of similar together

For instance following company names are there.

XYZ Supply Co Atlanta

XYZ Supply Co Lawrenceville

XYZ Supply Co Hartford

XYZ Supply Co Philadelphia

ABC Distribution Co Atlanta

ABC Distribution Co Lawrenceville

ABC Distribution Co Hartford

ABC Distribution Co Philadelphia

What this means is they are effectively same account but are from different offices across the USA. An ideal solution is to club together similar names in "XYZ Supply Co" and "ABC Distribution Co" etc. But when a user interact in the filter pane I want dashboard to consider all those aggregated values. Eg: when the user select "XYZ Supply Co" the value should aggregate all of the location and club it together in the dashboard.

Any idea how to do this?

Sanil

[MK9885 · Accepted solution]

You can create an XL file or Inline load in script..

If you do not have hundreds of companies then below steps should work ...

CompanyMap:

Load * Inline [

Company	Abbreviation
XYZ Supply Co Atlanta,	XYZ Supply Co
XYZ Supply Co Lawrenceville,	XYZ Supply Co
XYZ Supply Co Hartford,	XYZ Supply Co
XYZ Supply Co Philadelphia,	XYZ Supply Co
ABC Distribution Co Atlanta,	ABC Distribution Co
ABC Distribution Co Lawrenceville,	ABC Distribution Co
ABC Distribution Co Hartford,	ABC Distribution Co
ABC Distribution Co Philadelphia,	ABC Distribution Co

];

Use Abbreviation in front end for filters as new field and company field for your aggregation?

[#3]

But the name of these are continuously growing and I am not sure until I know it.

[#4]

I like that idea though.

[joseduque]

Hi,

You can use the wildmatch function:

Example the field name would be Company and you have this data

Company:

XYZ Supply Co Atlanta

XYZ Supply Co Lawrenceville

XYZ Supply Co Hartford

XYZ Supply Co Philadelphia

ABC Distribution Co Atlanta

ABC Distribution Co Lawrenceville

ABC Distribution Co Hartford

ABC Distribution Co Philadelphia

So in the Load of your table you have to create one field that has the wildmatch function like this:

if(WildMatch(Company,'XYZ Supply Co*')=1,'XYZ Supply Co',

if(WildMatch(Company,'ABC Distribution Co*')=1,'ABC Distribution Co','Others') )

You can Attach more conditions so you dont need to aggregated manually in a inline or xl file

[#6]

Thank you Jose. It worked.

[joseduque]

Hi Sanil, Your welcome by the way you marked arvind654 corrected answer  

[#8]

Both technique worked for me. I had over around 3000 company names, so I had to use excel to make a list and load it. Thank you guys. I really appreciate it, I am very new to Qlik, just one week experience. But I found it good, not easy as Tableau but more scalable.