Make a column a constant

[BenJones13]

Hi All!

I want to set the column name as a variable, in this case a constant (I think this is how Alteryx terms it). 

	Account Number	27/01/2023	28/01/2023	29/01/2023	30/01/2023
Total Sales	123	10	12	12	123
Total Expenses	234	32	15	16	234
Total Income	345	33	84	5	345

Basically, I want my constant to be [30/01/2023]. I'm currently transferring data from a trial balance into this spreadsheet, I insert my Account Number values into [30/01/2023], then using the Find and Replace tool to match the columns and populate into 30/01/2023 with the real values (above). There are, of course a lot of expressions and formulas to total and subtotal a bunch of columns in the process.

HOWEVER, down the line, when I want the same for the the next date, I really don't want to have to edit every single forumula I have and update the old date ([30/01/2023]) with the new one.

I want to know if there is a way to set a variable/constant as New_Date = [30/01/2023]. I would then use New_Date in my expressions and formulas, and would only require to change the value of New_Date at the beginning of the workflow when I need to move to the next desired column!

[alexnajm]

Hi @BenJones13,

This should work! I went for speed so let me know if you need me to explain it further :)

-Alex

[alexnajm]

@BenJones13 essentially the Transpose tool allows us to pivot the data down, but dynamically for when new columns get introduced in the future. Then we grab the Max date to be appended to all rows, and CrossTab the data back into the original format. The CrossTab does unfortunately replace punctuation with underscores, so the last Dynamic Rename tool replaces those underscores with the original slashes.

[DataNath]

Hey @BenJones13, this looks like a perfect opportunity to use Workflow Constants. If you just hit any whitespace on the canvas and then go to the Workflow tab, you can add them there:

Then you can reference it in formulae:

Now if you change it at the source (Workflow constant), it'll be reflected when you next run:

[BenJones13]

Hi DataNath,

This is the exact approach I've tried! However whenever I set my constant to 2023-01-22, it uses the value. I want to set the constant equal to the column!

For example, I have an expression like this:

 ABS(ToNumber([2023-01-22]))

I am simply just making the negative values positive. When I change it to: ABS(ToNumber([User.New_Date])) with User.New_Date = 2023-01-22 it populates my entire column with "2023".

If I set User.New_Date = [2023-01-22], my attempt at referencing the column, it populates the entire column with 0.

Struggling to find a way to reference the entire column!

[DataNath · Accepted solution]

Overlooked that, sorry @BenJones13! I'd explore @alexnajm's idea as well - haven't had a chance to look myself. Failing that, off the top of my head right now I can only really think of creating a batch macro where your control parameter is linked to all instances of the date that would need changing. Would be quite a bit of hassle to set up initially is all.

[BenJones13]

Hi DataNath,

Apologies for the really late reply. Now is the time where I will need to replicate this except with a changing date. I think the idea where you mentioned a batch macro and control parameter assigned to the date (essentially the changing output column) is exactly what I'm looking for. 

Would you be able to point me in the direction of some resources on how to do this? Sorry if it's an inconvenience, it's completely foreign to me! I know how it would be done, but not on Alteryx.

Really appreciate it!

Ben

[DataNath]

Hey Ben, just dropping a few resources here to get you started - feel free to ask Qs if needed!

https://www.youtube.com/watch?v=NNH_K5U5r78

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

https://help.alteryx.com/20223/designer/batch-macro