Forum Discussion

Qrew Trainee

3 months ago

Converting delimited text into checkboxes

Greetings, I have downloaded a publicly available spreadsheet from the FDA containing their warning letters and have imported into quickbase. It has worked well but I am stumped on how to do the fo...

formulas

MarkShnier__You
3 months ago
several ideas but let's take this step by step. If you create a new field of type Formula multi select text with this formula
Split(List(";",
[Cause 1], [cause 2], [Cause 3], .... etc, [Cause 9]))
then you will be able to use that field as a dynamic filter. It would not do any fancy percentage calculations for you but if there are not too many different causes then you could quickly filter your master list down and write down on a piece of paper :) how many came up on the filtered report as you cycle through each Dynamic Filter option in turn.
Do you have more ideas but why don't you start by doing that and post back.
Also can you tell me whether or not you have access to a master list of the causes, or do you need to generate them out of the data.

JohnD

Qrew Trainee

I think your intuition is correct. I want to take the blob of delimited causes and find a way to easily get counts on how many times each appears in the data and also be able to filter on those.

If it helps, here is a screenshot of the CSV imported into excel

What I am interested in is getting a good feel for the occurence of the various "subjects" in column E as those are actually the causes FDA cites in their warning letters. So I want to be ato say things like "43% of FDA warning letters include Misbranded as a cause". Or "In the prior two years, x, y, and z are the most cited causes for warning letters"

I am a quickbase newbie so felt checklist might be the way to go but am open to any good solution.

Does that help?

MarkShnier__You

Qrew Legend

3 months ago

several ideas but let's take this step by step. If you create a new field of type Formula multi select text with this formula

Split(List(";",
[Cause 1], [cause 2], [Cause 3], .... etc, [Cause 9]))

then you will be able to use that field as a dynamic filter. It would not do any fancy percentage calculations for you but if there are not too many different causes then you could quickly filter your master list down and write down on a piece of paper :) how many came up on the filtered report as you cycle through each Dynamic Filter option in turn.

Do you have more ideas but why don't you start by doing that and post back.

Also can you tell me whether or not you have access to a master list of the causes, or do you need to generate them out of the data.

JohnD
Qrew Trainee
3 months ago
There are at least 100 unique causes listed. I know that because I did convert each cause into a separate column using the "/" delimiter in the data. Just for the first word prior to the first "/", there were 95 entries in the resulting table.
I'll create the new field you suggest.
- JohnD
  Qrew Trainee
  3 months ago
  And, to be direct - I do not have access to any master lists of causes. I am actually trying to reverse engineering that.
- MarkShnier__You
  Qrew Legend
  3 months ago
  Once you have more than 100 choices for that dynamic filter field, my guess is it's not going to behave nicely and give you a drop-down list so it won't be very useful. Also with that number it's way too many to do statistical analysis.
  Now that I have a better idea of what you're trying to accomplish, I think the right way to go here is to set up a structure where there is a master table of causes and of course your table with the imported data, and then you would have a middle or join table called FDA Cause Assignments.
  One FDA Cause would have many FDA Cause Assignments.
  One FDA Warning Letter would have many FDA Cause Assignments.
  Then we would have to set up a Pipeline to automatically create those child records when the FDA warning letters are imported. By having that middle table it will allow you to easily do all kinds of wonderful analysis.
  The question now is for me to explain to you how to do the pipeline to create update the unique table of FDA Causes as they may appear in existing or future Warning letters as well as the FDA Case Assignments.
  If you have a small budget we could probably knock off this project in a couple of hours on a Zoom call, or else we can slowly plod through it on this forum here as my time allows.
  For sure, that relationship structure is the way to go and a Pipeline is the way to keep it automatically maintained so that as you import future FDA Warning letters everything will magically update .
  Mark Shnier
  Your Quickbase Coach
  QuickbaseCoach.com
  mark.shnier@gmail.com