How to apply one common filter for two associated tables.

[Ipsita]

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

ProductName	Products.Protocol	Products.State	Products.District	Status
Ponds	Beauty	MP	Indore	Sold
Curtains	Household	AP	Secunderabad	Sold
Bedsheet	Household	KA	Bangalore	Ordered
Gas Stove	Kitchen	MH	Pune	Ordered
Spoons	Kitchen	MH	Pune	Updated
Lakme	Beauty	MH	Satara	Updated

Product Status

ProductName	ProductStatus.Protocol	ProductStatus.State	ProductStatus.District	Status
Ponds	Beauty	MP	Indore	Updated
Olay	Beauty	AP	Secunderabad	Updated
Bedsheet	Household	KA	Bangalore	Sold
Gas Stove	Kitchen	MH	Pune	Ordered
Olay	Beauty	MH	Pune	Sold
Glass	Kitchen	MH	Satara	Ordered

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

[CELAMBARASAN]

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 ......;

[Ipsita]

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

[jonathandienst]

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

[Ipsita]

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]

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.