Cross Tab - values issues

Question

Current Excel File

Desired Future output

Hello all,

I am new to Alteryx and I need some help with this process.

The first picture contains the data I am pulling from a database. and the second picture is the desired output based on the data contained on the first sheet.

I want to do a little bit of a transpose/crosstab with this data but i don't really know how to go about it, I was hoping I could get someone to help.

From the column called type from the Excel spreadsheet or the screenshot above, I would like to grab unique values and transpose them so that they become their own columns, then I would like each one of them to have the columns  (gpm), SIFI and Comment under them and have the values of those three columns per ID for those 3 sub columns.

I tried the crosstab tool but i can't seem to have it work the way i want it to.

Can anyone help?

Thanks

Sample.xlsx

jasperlch · Accepted Answer

Hi @kshadrac

As far as i know, merged cells cannot be created in Alteryx. The closest result I can get is in the workflow attached.

kshadrac · Accepted Answer

Hey @jasperlch this solution will do for now. Thanks for the help!

Do you know if there are any tools in the Reporting Tab like the Layout Tool or the Table tool that can help me rearrange the desired output manually and help have that final desired layout?

jasperlch · Accepted Answer

Hi @kshadrac,

I think it will be hard to achieve exactly what you want. The problem is that Alteryx cannot handle multiple Field Names/Headers for each column. The closest result I can get is below:

Basically here we are treating even the top and 2nd headers as data values and unioning them back with the actual data values and then outputting the results to Excel without the "field names". But there are at least 2 downsides:

1. The merged cells are still not created. e.g. we are repeating the headers "INTERMEDIATE ANNULUS GAS" 3 times across 3 columns

2. this is more crucial: Since we are asking Alteryx to treat the top and 2nd headers, together with the actual data as data values in the same column, Alteryx now thinks of them as the same data type, which has to be a String type because of the top and 2nd headers. This forces Alteryx to treat the numbers "2" or "4" or other numeric values in the (gpm) columns as Strings too. This is why you will see a little green triangle at the top left corner of these cells in the resulting Excel file. This will create issue if you would like to create formula on Excel based on these numeric values.

Hope this explains the possible solution and its issues clearly.

Thanks,

Jasper