Pivot/unpivot help Magic ETL

Cbrack

I have a dataset which looks like this before i pivot it:

But I would like for it to look like this. Please ignore the fact there are duplicates, for example 2 IHS rows, I can fix that in the etl. I just want to pivot it and I'm struggling on something which seems to be easy. Thank you

david_cunningham

@Cbrack here is an example

master_group would be whatever your row-level identifier is. You would need to create a new column for each of the values you want to pivot out.

david_cunningham

@Cbrack if you're saying that you have some rows that are "PTD" and some that are "PTD "?

If so, you would want to clean that up before doing the pivot.

You can do that with the following function.

SQUASH_WHITESPACE() works as follows…

All sequences of whitespace characters, non-breaking spaces, control characters, and null characters (U+000000), are replaced with a single space (' '). Spaces at the beginning and end of the string are dropped.

david_cunningham

@Cbrack I just didn’t have anything to group by with the example data I generated to build your desired output. In your case, it could be a constant (Chem Data), or if you wanted to have multiple rows, you could group by something else (for example month or quarter).

david_cunningham

@Cbrack - perhaps I'm misunderstanding you, but to achieve your desired output you should be able to load in the group as your column and then average your qty_percentage in the value. For example.

Or are you saying that you want to turn your rows into columns in your ETL?

MichelleH

@Cbrack There are a couple options you could use, one within MagicETL and one in Analyzer:

• The Pivot tile in MagicETL will convert your rows to columns as you're describing. From your screenshot it looks like you only have two columns in your data, so you would need to add a dummy column for the "Select the column(s) that identify a row" drop-down
• If your desired outcome is to have this data pivoted in a table, then you could leave your dataset as-is and use a Pivot Table card with "Group" in Columns and Qty_Percentage in the Values. If you anticipate needing to add or subtract Groups from your data, this would be the more flexible option.

Cbrack

Thanks, so yes, i would like the end result to be in the ETL, not in analyzer. How would i need to fill out the pivot? I've tried so many different combo's, i'm starting to get confused on what I tried vs. what i haven't tried. Seems simple, but yet, I guess I'm new at it and this is a very basic start. Thanks again

david_cunningham

@Cbrack here is an example

master_group would be whatever your row-level identifier is. You would need to create a new column for each of the values you want to pivot out.

Cbrack

Thank you. That worked. I created a constant and called it Master group, with a text of "Chem Data". believe that's what you meant by the "lowest Level". In my source data, some of the group's contain extra spaces. Like PTD is PTD (2 spaces). is there a way to trim those spaces for the compare?

david_cunningham

@Cbrack if you're saying that you have some rows that are "PTD" and some that are "PTD "?

If so, you would want to clean that up before doing the pivot.

You can do that with the following function.

SQUASH_WHITESPACE() works as follows…

All sequences of whitespace characters, non-breaking spaces, control characters, and null characters (U+000000), are replaced with a single space (' '). Spaces at the beginning and end of the string are dropped.

Cbrack

Sorry, last question, I understand what the first squash_whitespace does, but what is the purpose of the second Master_group 'group label". Not sure what that is doing. Thank you.

david_cunningham

@Cbrack I just didn’t have anything to group by with the example data I generated to build your desired output. In your case, it could be a constant (Chem Data), or if you wanted to have multiple rows, you could group by something else (for example month or quarter).

Cbrack

Thank you so much for your help David