Hello-
I have two data tables, one for charges and one for assignments. I want to make a chart that shows each character, what dept they were assigned for the week, and how many charges they made so we can do a comparison of how people are charging.
I am running into two problems.
- My characters have two different names in the data sources and while I did try to relate them it does not seem to be working. I did a calculated field to make the "Charges" name the same as the "Assignment" name, but you cannot relate from a calculated field.
- This is causing issues where my "assigned" names are correct for the column but the data is not. For example. My chart is showing Ms. Puff as assigned the correct category, but the charge displaying is for Squidward. According to Assignments, data for Squidward should only display under the "ACS D" Dept column for that week.
- Some of my data is not plotting. On the weeks that one of the depts has no one assigned, the data comes in NULL for everyone. I tried leaving blank, putting in null, and finally just putting in a "None Assigned" name and still won't plot.
- Example. Mr. Krabs has a charge for this week but is coming in Null, but it should be under his assignment for the week of '3rd".
workbook attached if you think you can help. I'm stumped and so tired of looking through articles trying to figure this out.
Thanks
Hello @Sarah Gautam,
About your issue #1, actually you cannot use calculated fields in joins, but you can use Relationship Calculations:
I've tested it and joined on these formulas:
UPPER([Provider Assigned]) = REGEXP_EXTRACT([Billing Provider Code Name1], "^\d+\s+(\w+)")
(I could have copied-pasted and nested your different formulas for provider name, but I find my regex simpler. If ever you do not know regexes, ^ means the beginning of the text, \d+ is a series of numbers, \s+ a series of spaces, and (\w+) is the word I want to get.)
For your issue #2, I have to confess I do not understand the difference between charge and assignment (if I'm in charge of something then it's assigned to me?). I took your word about putting a "None Assigned", but this is just a blind guess. If it's not want you wanted, you'll have to explain a bit more the logic of your data.
IFNULL([Assignment], "None Assigned")
Test workbook attached.
---
Antoine Dinimant
Freelance consultant, trainer & teacher
