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 Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Determine which columns exist in dataset.

Evils
6 - Meteoroid

Hello. 

 

I have a data containing multiple columns and in some case's some of the columns may or may not be there.

 

Let me explain: 

1. This workflow is used by many people, of which different columns may exist.

2. The problem is if a column doesn't exist, my workflow wouldn't be executed. 

3. I want Alteryx to determine if the column exist and which one should be imported from the dataset.

 

My income data could look like this: 

TypeType code

A

10
B12
C11
A10

 

The problem is that the data in the [Type Code]-column, may have another name in next income dataset, like: 

TypeKey code

A

10
B12
C11
A10

 

And a third dataset could look like this: 

 

TypeAssembly code

A

10
B12
C11
A10

 

How can i determine when importing the data from an excel-workbook which of the columns contain the code?

In must case's only one of the three columns ([Type code], [Key Code] or [Assembly Code])  exist in the dataset. But there might be some case's where two of the three columns does exist, but one of them will be empty. 

 

In addition, when Alteryx have "detected" the correct column, I would like to rename it e.g [Code]

 

Hope you guys can help me - thanks :) 

 

8 REPLIES 8
Ladarthure
14 - Magnetar
14 - Magnetar

You might look forward to input the data without headers (in your file input) and then filter to have the first line the title, this done, you may rename using . a select and use a dynamic rename.

 

(attached a sample workflow with 2 possible solutions depending on your case)

 

(hope it helped)

Capture d’écran 2018-10-09 à 11.02.09.png

Evils
6 - Meteoroid

Any chance you can upload your import data ;) ? 

 

Ladarthure
14 - Magnetar
14 - Magnetar

I just used the first table in your post in an excel file :)

Evils
6 - Meteoroid

Haha, my bad  :) 

 

I understand what you are trying to do, but its not exactly what I was looking for. Maybe it's becuase I explained the problem wrongly (probably because my english ain't the best). 

 

The case is: I will once a week get an excel-file with tons columns, sometimes it contains  [Type code], [Key code] and [Assembly Code] column and other times it only contains one or two of the columns. Since the firm dont have any standard for, where to type in the values, some people write the values into [Key Code] others to [Assembly Code] and sometimes the values is typed into [Type Code]. 

What I know, is that only one of the three columns contain values, while the others will be empty/null. 

 

What i want to do, is to make an Alteryx-workflow-template, which can "detect" how many of the above columns exist in the excel-file and which one is containing the values. 

 

I was think the 'Dynamic selection'-tool with an integrated function could be a solution, but can't make it work. 

 

The renaming part i will figure out afterwards :) 

Ladarthure
14 - Magnetar
14 - Magnetar

You could use a batch macro to merge all the files and then either rename the fields or use a dataprep function to do so!

 

(attached a basic workflow with a batch macro to open multiple files)

danrh
13 - Pulsar

Something like this should do the trick:

image.png

As long as you only ever get a single code in any of the three columns, this will consolidate them and create a new column named "Code". Take a look, see if it makes sense and if it fixes your issue.

Evils
6 - Meteoroid

Thanks :) ! 

Evils
6 - Meteoroid

Danrh Thank you very much ! 

Labels