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

Update number in formula and update select

emso
5 - Atom

Hi 

I am new to Alteryx and in a bit of a trouble. 

I have a simple piece of code which generates a dataset which is outputted as a CSV.

However I need to make 193 datasets. 

I have attached a snip of my data and my code. 

 

What I need: 

1) To run the code where the variable "DATASET" is set to every number between 42 and 234. (creating 193 datasets)

2) The select needs to be updated each time, only columns where the left(column name, 2) < DATASET, should be included in each dataset.

Also [LPERMNO] and [Navnefil] should be included after the select. (This I could manage in the dynamic select)

 

My tries: 

1) I have tried to make an iterative macro which updated the "DATASET" with 1 for each run, however I could not manage to do it correctly. 

2) I have tried using dynamic select, however I cannot use the value from DATASET and thus I don't know how to correctly do this.  

 

The only option I see now, is to run it 193 times, updating in both the formula and the select.. 

Is there anyway this can be done not manually? 

 

Best regards

Emilie

3 REPLIES 3
apathetichell
18 - Pollux

O.k. - so the approach I'd look at would probably be a generate rows so you can create rows for everything from 42-234. I'd transpose my data so my columns would be in name and their value can be in value - then I can do one set tonumber(left([name],2) to see if it's less than the applicable dataset row. 

 

 

After appending are you seeing each datapoint with a seperate column or is it a sum figure? If it's a sum figure I'd do it with a running total and it's a fairly straightforward compare.  If not you'll need to re-crosstab it. If it's a sum I'd do an append to table after transpose/running total. 

 

It's a bit messy as an append vs common macro (you're going to have 193 versions of your transpose data to start with.) but it's nothing Alteryx would have a problem with.

apathetichell
18 - Pollux

one more - your left() command is set to 2  but some of your numbers are 3 digitis - you won't capture them. I used a regex_replace to get the numbers.

 

Wasn't sure what you wanted so I transposed everything back. The null()s are what didn't meet what I understood to be your criteria.

emso
5 - Atom

This is absolutely perfect! Now I can easily remove the null columns from the datasets, which don't need them all. 

And good with the regex_replace, had slipped my mind that some were bigger than 2 digits! 

Thank you so much! 

This saves me so much time, my thesis thanks you!! 

Labels