Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ipsitafujitsu
New Contributor

How to apply one common filter for two associated tables.

Hello Team,

Could you please help me in getting the results applied from a common filter value from two associated values.

Scenario: I have two tables named Products and ProductStatus which has some common fields in both the tables. I associated both the tables by ProductName field. Now I want to use common filter for Protocol, State and District which will show me the results from both the tables.

For example, I have taken ProductName as Dimension and measures as count of Protocol and I have filter like Protocol, State, District. Now I want if I will select Protocol as Beauty from Filter Category then it should show me Products as Ponds- 2 counts,Olay - 2 counts  and Lakme - 1 count.

To be more precise, I want result from both the tables on selection of a common filter to make it more user friendly.

Products

ProductNameProducts.ProtocolProducts.StateProducts.DistrictStatus
PondsBeautyMPIndoreSold
CurtainsHouseholdAPSecunderabadSold
BedsheetHouseholdKABangaloreOrdered
Gas StoveKitchenMHPuneOrdered
SpoonsKitchenMHPuneUpdated
LakmeBeautyMHSataraUpdated

Product Status

ProductNameProductStatus.ProtocolProductStatus.StateProductStatus.DistrictStatus
PondsBeautyMPIndoreUpdated
OlayBeautyAPSecunderabadUpdated
BedsheetHouseholdKABangaloreSold
Gas StoveKitchenMHPuneOrdered
OlayBeautyMHPuneSold
GlassKitchenMHSataraOrdered

Please let me know will it be possible to get this from one sheet with a common filter category or I will need to have two separate filters.

My Thought: Can't we associate two tables with more than one field column so that we can filter with common values. As of now I associated ProductName so that it can be combined from both the tables. Similary can we do for Protocol, State, District?

I need this requirement very urgently. Please help me with your answer.

Any help is highly apprieciated.

Thanks,

Ipsita

5 Replies

Re: How to apply one common filter for two associated tables.

If all the columns are going to be same in both the tables. Its better to keep the column names same and make it concatenated into single table.

if there will be different columns then

Create a combined key in both the tables and keep the Fields used to create the combined key in Master table alone.

//Considered Product is the master

Product:

LOAD

     ProductName,

     Protocol,

     State,

     District,

     Status

     Autonumber(ProductName & '_' & Protocol & '_' State & '_' & District) as %ProductKey

FROM .....;

ProductStatus:

LOAD

     Autonumber(ProductName & '_' & Protocol & '_' State & '_' & District) as %ProductKey

     Status as ProductStatus

......

From ......;

ipsitafujitsu
New Contributor

Re: How to apply one common filter for two associated tables.

Hello, Thank you so much for your reply.

Just to clarify, both the tables do not have all the same columns. There are some additional columns in Product table.

To go with briefly to my understanding is, we cannot use filter option to filter out the result from two tables only by associating the field via Data Manager. Is this the correct understanding?

Again I have one query related to your provided solution. As I uploaded my tables via Data Manager and did the associations so shall I again be able to load data from Data Load Editor? Because my Product table is again used for other reports as well.

I am really not confident enough how to do with load editor. I may be asking you silly things but could you make me understand what does this

Autonumber(ProductName & '_' & Protocol & '_' State & '_' & District) as %ProductKey query mean.

Can you advice me with the steps which will help me to achieve the output.


Regards,

Ipsita



MVP
MVP

Re: How to apply one common filter for two associated tables.

I would concatenate the two into a single table in your model, like this:

Data:

LOAD

  ProductName,

  Products.Protocol as Protocol

  Products.State as State,

  Products.District as District,

  Status,

  'Product' as Level

  ... other fields ...

FROM ....

Concatenate(Data)

  ProductName

  ProductStatus.Protocol as Protocol,

  ProductStatus.State as State,

  ProductStatus.District as District,

  Status,

  'ProductStatus' as Level

FROM ...

Now you can simply select the field you need. Use {<Level = {Product}>} or {<Level = {ProductStatus}>} to differentiate the two data sets

ipsitafujitsu
New Contributor

Re: How to apply one common filter for two associated tables.

Could you be please more specific to your explanation. I could not able to run your query in my Load Editor.

It is throwing me "Autonumber is not a recognized built-in funtion name" error message while loading data.

Below is the actual script which I was trying to load.

LOAD ProtocolCalendarRecID,

UniqueProductID,

ProductName,

ProtocolTitle,

ProductUsed,

GrowerRating,

ProductUsedStatus,

CropName,

GrowerProtocolDataID,

Expr1,

GrowerProfileID,

GrowerName,

StateName,

DistrictName,

CreatedTimestamp,

CalendarName;

[viewProtocolProducts]:

SELECT ProtocolCalendarRecID,

UniqueProductID,

ProductName,

ProtocolTitle,

ProductUsed,

GrowerRating,

ProductUsedStatus,

CropName,

GrowerProtocolDataID,

Expr1,

GrowerProfileID,

GrowerName,

StateName,

DistrictName,

CreatedTimestamp,

CalendarName,

    Autonumber(ProductName & '_' & ProtocolTitle & '_' & StateName & '_' & DistrictName) as %ProductKey,

FROM Anantham2Prod.dbo.viewProtocolProducts;

LOAD ProtocolCalendarRecID,

GrowerProfileID,

ProtocolCalendarPhaseID,

UniqueProductID,

ProtocolCalendarID,

UniqueCropGrowthPhaseID,

ProtocolTitle,

CropName,

CalendarName,

ProductName,

GrowerName,

StateName,

District as DistrictName,

Status;

[ProtocolProductUnsed]:

SELECT ProtocolCalendarRecID,

GrowerProfileID,

ProtocolCalendarPhaseID,

UniqueProductID,

ProtocolCalendarID,

UniqueCropGrowthPhaseID,

ProtocolTitle,

CropName,

CalendarName,

ProductName,

GrowerName,

StateName,

District as DistrictName,

Status,

    Autonumber(ProductName & '_' & ProtocolTitle & '_'& StateName & '_' & DistrictName) as %ProductKey,

FROM Anantham2Prod.dbo.ProtocolProductUnsed;

saniyask
Contributor

Re: How to apply one common filter for two associated tables.

Hi Jonathan,

Regarding your first query-- yes you will be able to load the data from Load Editor even after loading it through data manager. You can simply select the files from your data connections and create your own association based on your requirement.

Regarding Autonumber function it will generate a Unique ID against the parameters that you have passed.

in this case Autonumber(ProductName & '_' & ProtocolTitle & '_'& StateName & '_' & DistrictName) as %ProductKey, %ProductKey will store the unique ID for the composite key.

Regards,

Saniya.