Forum Discussion
Changing mismatch 'Blank' with text
Hi,
I want to create a visual that is using data from 3 related tables but encountered some mismatch issue. The sample tables look like this:
Table A
| Item Number | Volume |
| a | 10 |
| a | 5 |
| b | 11 |
| null | 7 |
Table B (no null value in this table)
| Item Number | Category Number |
| a | 001 |
| b | 002 |
Table C (no null value in this table)
| Catogory Number | Catogory Name |
| 001 | Grocery |
| 002 | Kitchen |
As I want to create a pie chart presenting the volume distribution by item's Catogory Name, I got some 'blank' because there is 'null' value in the first table (as below).
Known I cannot combine the existing 3 tables into 1, and now I want to change the 'blank' in my pie chart to 'unknown'. Since the 'blank' here is not a value but a representation of mismatch. How can I achive this? Thanks!
My attempts:
1. change the null value in Table A to unknown -- fail
2. append a row with null value to both Table B and C, and try to link "null" with "null" -- still not working
Anonymous you need to have something that will represent the values in table A also in table B (and in this case also table C cause they are in 1:1 so they actually should be 1 table). If you replace the null with unkonwn you also need to have the same unknown text in table B also with unknown text for category number and also in table C unkonwn text on the values of the new row you need to add.
Better to join table B and C before you bring it to the model, then you will need to do that only on one table.
5 Replies
- Hariharan_R
Solution Sage
Hi,
To achieve this in simple way, you can uncheck the blank on the visual filter.
Thanks
Hari
Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.
My Blog :: YouTube Channel :: My Linkedin - SpartaBI
Community Champion
Anonymous the blank appears when you have records in the many side of a relationship that doesn't have a matching key in the table at the one side. You can't replace it. You should always strive to have a model that each row in the many side has a represntative in the one side. Can you share a screenshot of the model view and write what columns play in the relationship.
- SpartaBI
Anonymous you need to have something that will represent the values in table A also in table B (and in this case also table C cause they are in 1:1 so they actually should be 1 table). If you replace the null with unkonwn you also need to have the same unknown text in table B also with unknown text for category number and also in table C unkonwn text on the values of the new row you need to add.
Better to join table B and C before you bring it to the model, then you will need to do that only on one table.
