Pivot Transformation in Magic ETL not Pulling in 4 columns? HELP

Hulachic5
Hulachic5 Member
edited October 2021 in Magic ETL

Aloha team:

Data is coming from Survey monkey; therefore, I have to transform the data from rows to columns.

I have 18 columns that I do this with; using one pivot transformation, since all the data is similar (i.e. #1, 2, & 5) (1st pic).

image.png

The issue is 12 columns are able to transform the data, however, four (2nd pic) is coming in null.

I have checked the actual labeling from the source data and it is correct; however, it pulls in null.

image.png

I know there is data because I isolated (using a filter) to see the data. It doesn't like something about the label or something. I tried taking away the HTML tags (didn't work). The labels and data are in the 3rd pic.

Any assistance would be greatly appreciated. I am at my wits end. ;-(.


image.png


Comments

  • GrantSmith
    GrantSmith Coach

    Hi @Hulachic5 - Do you have any whitespace at the end of your string that isn't being accounted for? Have you copied and pasted the value into your Pivot tile?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Hulachic5
    Hulachic5 Member

    HI GrantSmith:

    Yes, the label is copied/pasted directly from the data table.

  • MarkSnodgrass
    MarkSnodgrass Coach

    @Hulachic5 I have limited experience with the Survey Monkey Connector, but I see that there is a Questions_Question_ID column that seems to coincide with the Pages_Question_Heading column. Have you tried using that column as your pivot column rather than the Pages_Question_Heading column since matching a numeric value will be a lot easier than a long text string?

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ChristianW
    ChristianW Member

    What about adding a step before to make the pivoting more stable? You can use the formula tile to build a CASE statement that renames each observation, for example:


    CASE WHEN 'PAGES_QUESTION_HEADING' LIKE '%What is your last name?%' THEN 'PFC Last Name'

    WHEN 'PAGES_QUESTION_HEADING' LIKE '%Hours did you...%' THEN 'Hours Worked Day 1'

    .....

    ELSE 'Whatever you need'

    END

    I think you get the idea of this. Once you have covered all the questions and normalized the attributes, your pivoting should not have this problem, It seems that something is changing in your data source, either a space, character, or something else, hard to tell.

    Hope that this idea helps :)

  • Hulachic5
    Hulachic5 Member

    @ChristianW - Your recommendation was a success. I was able to clean up the label and then pivot the revised label name.

    It is true that there were hidden spaces...as indicated by @GrantSmith; I just could not find them.

    Truly appreciate this community of GREAT MINDS!


    Kudus to you both.


    Salina

  • ChristianW
    ChristianW Member

    @Hulachic5 I am glad that it worked!

Categories