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: 
Not applicable

Club values in Filter Pane and Applymap

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

1 Solution

Accepted Solutions
MK9885
Master II
Master II

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 [

CompanyAbbreviation
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?

View solution in original post

7 Replies
MK9885
Master II
Master II

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 [

CompanyAbbreviation
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?

Not applicable
Author

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

Not applicable
Author

I like that idea though.

joseduque
Partner - Contributor III
Partner - Contributor III

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

Not applicable
Author

Thank you Jose. It worked.

joseduque
Partner - Contributor III
Partner - Contributor III

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

Not applicable
Author

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.