Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

SmartSheet -- working with nulls

VizED
6 - Meteoroid

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:

 

SectionDeliverableTask
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.

SectionDeliverableTask
Acquisitionnullnull
nullClosingnull
nullnullTask 1

 

 Or, this is actually what I get in Alteryx:

Alteryx_Smartsheet_NullMembers.PNG

 

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:

 

SectionDeliverableTask
AcquisitionClosingTask 1
AcquisitionClosingTask 1
AcquisitionClosingTask 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

 

1 REPLY 1
VizED
6 - Meteoroid

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

Labels