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

dynamically selecting month column

Elias
6 - Meteoroid

Good afternoon community,

 

I have a dataset containing sales values for each month. Each column header is a number referring to the month it represents (eg Jan is 1, Feb is 2 etc).

From the filename, I'm able to extract the month name for which I should be running the report (eg. September).

 

How can I have Altreyx Identify that I am after column '9', rename it Sales Value, and exclude all the other month columns?

 

I had a look at a solution using the dynamic rename tool using the formula option but couldn't figure out how to get a variable working.

 

Thanks

 

6 REPLIES 6
MarqueeCrew
20 - Arcturus
20 - Arcturus

@Elias,

 

Could you post a single record going into the desired function?

 

Your request is doable, but it is a little unclear.  In seeing the formats of all of the column names, a solution specific to your data is possible.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Elias
6 - Meteoroid

This is what one record + headers look like:

 

123456789
211737303210112114
MarqueeCrew
20 - Arcturus
20 - Arcturus

@Elias,

 

I thought that the filename would be present (that's how you determined September).  Please clarify that.  Your desired output is:

 

Sales Value

14

 

One column output (as all other month numbers are deleted).  No other data is output.

 

Right?

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Elias
6 - Meteoroid

You're right, there is also a column that brings in the Filename which in this case would be September.

 

You're correct on the desired output.

 

(Ideally, the solution could be repeated for every month without needing any human input)

 

Thanks Mark for looking into this!

MarqueeCrew
20 - Arcturus
20 - Arcturus

@Elias,

 

The workflow will read in multiple records and produce 1 column of output for only the column of data with a numeric value matching an incoming MONTH field.

 

capture.jpg

 

A record number is assigned to each incoming record.  The data is transposed to Record ID + MONTH + Column Name + Column Value rows.  If the Month Name = the "formatted" month number, then the row of data is kept.  The current month data is then cross tabbed back to a single column of data applying a name of Sales Value.  The record number is removed and you are left with a single column of values.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Elias
6 - Meteoroid

Thanks Mark for looking into this!

Labels