Hi folks,
I am working on a SmartSheet project where I was planning on using their Tableau connector to develop some dashboards for SmartSheet reporting.
SmartSheet is basically a spreadsheet. It is great for row-based data and can create row-based hierarchies for tasks and sub-tasks. My issue is that while the type of project I am working with has a parent:child:grandchild relationship, however it is based on columns not rows. SmartSheet allows for row hierarchies but not column-hierarchies.
What I mean is that the hierarchy of columns is (or should be): Project Section > Project Deliverable > Project Task with each of those being their own column. Basically every Task belongs to a Deliverable and every deliverable belongs to a Section.
When I create the sheet, from a hierarchical layout perspective, I get what I want:
Section | Deliverable | Task |
Acquisition | ||
Closing | ||
Task 1 |
However, when I connect via Alteryx or Tableau, not surprisingly, I see exactly what the sheet looked like in Smartsheet; of course with the blanks (the result of the hierarchy) rendered as nulls.
Section | Deliverable | Task |
Acquisition | null | null |
null | Closing | null |
null | null | Task 1 |
Or, this is actually what I get in Alteryx:
My question -- sorry for the preamble -- is what would be the best way to deal with the nulls?
It seems that if I was able to fill in all the empty fields with the appropriate name of the hierarchy's correct member, like the picture below, then I might be getting closer to being able to work with the data in Tableau:
Section | Deliverable | Task |
Acquisition | Closing | Task 1 |
Acquisition | Closing | Task 1 |
Acquisition | Closing | Task 1 |
I know this isn't an Alteryx issue, but I am hoping one of your transformation wizards can help me work this out!
Thanks!
-C
Solved! Go to Solution.
Solved it using the Multi-row Formula tool -- worked awesome!
Here's the thread with the answer
Here's the answer (the expression for the multi-row formula tool):
IF IsNull([Field_1]) THEN [Row-1:NewField] ELSE [Field_1] ENDIF