5 Replies Latest reply: Oct 31, 2017 8:37 AM by Saniya Shaikh RSS

    How to apply one common filter for two associated tables.

    Ipsita Mahapatra

      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

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

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

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

              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



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

                  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

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

                    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.

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

                    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;