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

Hulachic5 · 2021-10-19T18:05:57+00:00

There was an error rendering this rich post.

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

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.

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. ;-(.

Find more posts tagged with

Comments

GrantSmith

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?

Hulachic5

HI GrantSmith:

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

MarkSnodgrass

@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?

ChristianW

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

@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

@Hulachic5 I am glad that it worked!