Update number in formula and update select

Question

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

grundlag example data.yxdb

code.yxmd

apathetichell · Accepted Answer

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 · Accepted Answer

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.