Best Of
Re: Form Builder and Form Viewer Transformation
You are on the right track. I use form builder/viewer and here is what the main part of my ETL looks like that gets the data structured like you want.
I first filter to submitted entries by filtering to where submitted = true. This eliminates where people started filling it out, but then abandoned it.
The select columns tile reduces it down to the necessary columns that I am interested in.
In the pivot tile, start with this:
In the lower section, you will tell it what to look for an what to call it. In section 4, enter in the name of the field that you want to pivot exactly as it was entered in your form builder (including capitalization and punctuation.) For example, if you had a question in your form that said , "How many years of experience do you have?", you would need to enter that exact text in section 4. In section 3, you assign what name you want to give it as a column heading. I make 3 & 4 the same so there is no confusion as to what question it represents. Here is what some of mine look like.
This will get your data in the structure you want. You can use additional tiles like Formula or Alter Columns to change these new columns to specific data types to make it easier to build cards off of.
Hope this helps.
Re: How to make sure dashboards are organized the same way for users in a group (or across groups)
@tlammie1806 If you want certain pages to be in the same order for everyone then you have to create the pages in the 'Company page settings' in Admin>Company settings. This is the only way I know of to have pages stay in the same order. Users can customized the order of any pages you create using 'Manage Dashboards'.
Re: Split varying full names into First Name, Middle Name, and Last Name
Agree with @damen that there is not a perfect solution since you have to assume a single first name and single last name, but here are some things you can do to get close if you can't fix the source data. Leveraging the formula tile in Magic ETL, you can create formula fields and then reference within that same tile. Here are some useful ones to create.
Start by eliminating multiple spaces between words and beginning and trailing spaces with the squash_whitespace function
full_name = squash_whitespace(`full_name`)
find the length of the name
str_length = length(`full_name`)
remove all spaces and find the length
str_length_no_space = LENGTH(REPLACE(`full_name`,' ',''))
subtract the two to find the number of spaces
num_spaces = `str_length` - `str_length_no_space`
first name is straightforward using the split_part function
first_name = SPLIT_PART(`full_name`,' ',1)
last name will utilize the num_spaces with the split_part function
last_name = SPLIT_PART(`full_name`,' ',`num_spaces`+1)
middle name is going to be a little trickier. I would suggest utilize a case statement using the num_spaces field and then concat accordingly
CASE WHEN `num_spaces` = 2 THEN SPLIT_PART(`full_name`,2) WHEN `num_spaces` = 3 THEN CONCAT(SPLIT_PART(`full_name`,2),' ',SPLIT_PART(`full_name`,3)) END
You could extend this out further if there are additional spaces.
Hope this helps.
Re: Split varying full names into First Name, Middle Name, and Last Name
The best way to get 100% of the way there is to fix the data input. If that isn't a realistic option, splitting each word into columns and saying that the first word is FN the last word is LN and everything in between is middle might be the best option. Without knowing which name is exactly which, there is no 100% answer.
damen
Re: Multiple Event Types on a Calendar Card
This has now been added to the Ideas Exchange under: Multiple Events aligned on a single Calendar Card
Samuel.