Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Cloud Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Cloud.

hello, I need some help as Im completely new to Trifacta and cant find the solution. I have a data set with multiple columns. What I want to do is to separate the columns which column name contains word ” Satisfaction” and “Status” in it and create new

 
1 REPLY 1
APrasad_Tri
Alteryx Alumni (Retired)

Hi @Giorgi Gobronidze? , thank you for the question!

 

I have created a new recipe (New Column) from the existing recipe (Extract Columns) to get the new column with values. The output of (new column) recipe is joined with the initial (extract columns) recipe in another recipe (Final table). I have used inner join on $sourcerownumber.

I have added a new column $sourcerownumber to get the rownumber. You can also use ROWNUMBER() function to define rownumber. Any unique primary key will also serve the purpose to recreate the table structure.

 

To extract and find column names, we have to convert the table structure using Unpivot.

Select Unpivot from the toolbar as shown below

In the column section, select the Range or Multiple to pick the column names you want to search for the specified text.

 

Using IF() function and AND() function, you can select the row values with the words "Satisfaction" and "Status"

IF(AND(MATCHES([key], `Satisfaction`, false), MATCHES([key], `Status`, false)), key, NULL())

Clicking on the missing values in the data quality bar and using Trifacta suggestions, I deleted the missing values. 

We have got the desired columns and the values. Now, we will restructure the table again to bring it back to its original form using Pivot transform.

 

From the toolbar, Select the Pivot transform. Fill the new extracted column in the column, label and use ANY() function to input the same values.

Please note that rownumber is used as the row label to get the structure back to its original form. You can use the col_name from ROWNUMBER() function or a unique primary key also.

 

Merge the column to create the new column using Merge Transform from the toolbar.

 

I have used '-' as the separator. You can use any separator as per the requirement or '' blank.

 

 

 

In the final Table recipe, select the Join to transform from the toolbar and inner join with New Column recipe to get the final table with new column value containing columns from Satisfaction and Status.

 

Hope this helps. If this is not your final output then please do share the sample and help us understand the problem deeper.

 

Best,

Apeksha Prasad