Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ......;
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
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
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;
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.