This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a data set where the number of columns will shift. I know that I want to keep column "GROSS" and all of the columns before it.
I'm looking into the Dynamic Select tool, and have been able to select the "GROSS" column.
I'm at a loss on how to select the columns before it as well. Could I somehow determine the FieldNumber of the "GROSS" column and then do a select FieldNumber
Go to Solution.
Go to Solution.
I am not sure if it's the best way, but I'd do it differently.
1) Shift Columns to rows (Transpose Tool)
2) Multi-Row-Formula to determine all values before 'Gross' and adding a 'Keep' Column.
3) Filtering all unneeded columns (Filter Tool)
4) Shifting everything back (Cross Tab)
I have tried it with two different examples and it worked well. I'll attach my workflow for you. Let me know if it helps.
Thank you so much for the suggestion! Unfortunately, I gave it a shot and didn't produce the expected results.
The data isn't correctly retained after either the transpose or the transform.
Ahh, I see my error.
I have adapted the workflow. I am struggling a bit with shifting the columns back tho.
I'll try to figure it out while getting some food.
Current version is attached.
Apologies for the delay, I had to mock up some data. The attached is representative of the actual data.
I think I finally got it. I tried another approach this time. I should have gone with your first guess (Dynamic Select) in the beginning and we would have been quicker.
That totally worked!!! Thank you so much!
One tiny edit, the formula on the Multi-Row Formula tool was slightly offIF [Row-1:Keep] = 'No' OR ([Row-1:Name] = 'GROSS' AND [Name] != 'GROSS')THEN 'Remove'ELSE 'Keep'ENDIF
Since it was looking for 'No' but the formula was set to output 'Remove'. I simply changed the 'Remove' to 'No' and it worked like a charm!
IF [Row-1:Keep] = 'No' OR ([Row-1:Name] = 'GROSS' AND [Name] != 'GROSS')THEN 'No'ELSE 'Keep'ENDIF
I am glad we finally figured it out!
Ohh, looks like I forgot to change that. I'll edit my post and attach a fixed version in case someone will find this post in the future.