In Qlik Sense - I want to filter data on a dimension for a table.

[#1]

How do I filter data in a Dimension for a table in Qlik Sense?  I am not familiar with Qlik Sense Syntax and need some help.

I have a table with all of our part numbers for multiple companies, for the sheet / app that I am building, I only want data to load that is for one company.

intcompanyid is the field and i want to filter by company id 1

intcompanyid = 1   is what I tried to use but all of the other companies are still displaying.

Should I be doing this in the data model?  And if so what syntax to I use there?

I want to have a sheet per each company in the end.  Please advise, trying to find this answer within Qlik Sense is maddening.  Much easier in Qlik View.

This question is for using QLIK SENSE.

Any help would be greatly appreciated!!!!!!!!!!!!

[danansell42]

Hi

If you are to do a where clause in the script then it won't be possible to do a sheet per company in the front end as you would have excluded all the other companies.

Maybe best to use set analysis in all charts and tables.

Example:

Sum( {<intcompanyid = {'1'} >} Sales)

Thanks

Dan

[#3]

Dan,  Thanks for your reply however if I use the formula :

sum({<intCompanyID={'1'}>}intCompanyID)

I get an invalid dimension.

So am I to assume that the Expression Editor is not used for filtering but rather grouping, summing, etc...?

If I can't do via data load editor, then doing via each sheet would be good, but how?

table = Company

Field = intcompanyID

Data:  1,5,1002

I want to filter for this sheet to only show data for company 1.

[danansell42]

My first thoughts are is it due to a typo...

I notice you have stated the field is called intcompanyID rather than intCompanyID (Capital C difference).

Little confused why you would want to sum that field though.

Surely you are building a chart or table where you would just need to exclude all other companyID's except 1.

Therefore you would need to add the element of set analysis into every expression on that sheet.

sum(  {<intcompanyID={'1'}>}  ValueField )

If you share a document i may be able to look at it for you.

Thanks

Dan

[#5]

Dan,

Not building a chart, but rather a sheet with filters and a table.  In essence we have one part number catalog but several companies (about 5 or so).  I want to have my sheet only display one of the 5 companies.  So my thought was to on the table filter down to only the one company to display.  Don't want to sum anything just exclude all companies.  If I filter at the data load editor I can achieve this, however then I have to create a new connection to accomplish this.  It may be that what is simple in Sequel is not as straight forward here.  It seems that once you get on the sheet itself, there is no way to pre-filter the data without either using Set Analysis or filtering on the data load editor.  Is this correct?

In the Data Load editor, I was able to achieve the desired results by: 

FROM "Tools_Metrics".dbo."tbl_Catalog"

WHERE Company = 'Tools'

What I wanted to do was not filter here but on the table using the dimension to filter to only the one company I want to display in the table.  I think that I am probably going about this the wrong way and I don't think Qlik Sense works this way.

What is the best practice to filter like I am wanting to do? Via Data Load Editor or Set Analysis?  From what I have heard Set Analysis can be a bit tricky.

Thank you for your assistance.

[marcohadiyanto · Accepted solution]

Hi,

I think you should go to load editor and use where function.

From

Where intCompanyID=1;

Regards,

[danansell42]

If in the end you want to have all companies in the same application but displayed on separate sheets then using a where clause on the load script won't work as you will be excluded the other companies.

Would the end user not being happy to just select the company filter on the user interface?

Then you only require 1 sheet with no complicated expressions which satisfies the demand.

If you really do need a separate sheet for each company then i think set analysis on all the expressions within the table is probably the best way forward.

I have attached a very simple app where i've created some random data and duplicated the sheet 3 times (company 1, 2 and 3). I've put a single expression in the table to demonstrate how it could be done with set analysis.

I've also added an extra sheet with no set analysis. All the user would need to do is select the company from the filter and the results would be the same as the other sheets.

Hope this helps

Dan

[#8]

Thanks Dan, I believe I will have to look more into Set Analysis as I get deeper into this.  I assumed I would have to achieve at the data load screen as I believe the intent afterwards is to just interact with the data pulled down.  So that is what I ended up doing.  Thank you for your time.