Hi All !
I need help with dynamic multi-field situation.
Situation is below:
1. The number of Fields which differ by number suffix(1,2,3..) is NOT FIXED. So sometimes, there can be just two fields, Type and Type2. But sometimes it can be Type ~ Type 10 with 10 columns.
2. I need to build a dynamic formula which IF TYPE is blank, then use the Type2 OR WHATEVER COLUMN THAT HAS A VALUE.
At least the good thing is there will always BE JUST ONE column that has A VALUE. There will not be two or more columns with values. IF one column has a value, then REST columns will ALWAYS be empty. Just that the number of columns are not fixed but changeable.
Type | Type2 | Type3 | TypeXX.... |
11 | |||
22 | |||
33 | |||
46 |
Expected Outcome:
Type |
11 |
22 |
33 |
46 |
Thank you so much for your time.
Solved! Go to Solution.
Hi @mjh418
Best way to solve this to use transpose tool. It will work n number of type columns when they are set as values.
Workflow:
1. Using transpose to convert columns to rows.
2. Using filter tool to keep only non nulls.
3. Renaming and selecting required columns.
Hope this helps : )
Thank you for your response.
But this doesn't seem to work.
Sorry but there are actually other columns besides these that need to be retained.
Also, the Type1 actually is just Type without number.
The values that exist are in random locations not necessarily going from left to right.
@mjh418
I believe a sample data set for input and output is necessary here.
Hi @mjh418
All the other column which is not included in the calculation can be marked as key columns like below.
Output:
If you provide a sample file we can help you out accordingly.
Hope this helps : )
Thank you. I think it kind of works.
I have 2 follow up questions.
1. In the workflow, I would have checked for Type 1 ... to Type 4 for example. If the source data actually has until Type 11. Then ALL the type~type11 will automatically be CHECKED ON ? (It won't only check for Type 1 until 4)
2. If I actually have 3 different categories of TYPE, For example, TYPE1~3, then LEVEL1~3, CLASS1~3, how do I put these different categories back as columns after this? (not under one column but 3 columns)
Hi @mjh418
1st question:
You would need to make sure all other columns which doesn't need to be used in calculation are selected as keys. That all other fields will be included in the calculation.
2nd question:
a. You can handle them separately using 3 different transpose tools.
b. You can use a transpose tool - crosstab tool combo to apply and create multiple columns.
If you provide a sample file we can help you out much better.
Hope this helps : )
Thank you so much for your help by the way.
1. So you mean that as long as I designate the FIXED columns as KEYS, all the rest of them (dynamically) will be selected automatically as data columns no matter how many?
2. Okay so after applying your help, now it looks like this:
FIXED COLUMN1 | FIXED COLUMN2 | Name | Value |
xx | xx | A_Type1 | 11 |
xx | xx | B_Type1 | 555 |
xx | xx | C_Type1 | 3333 |
xx | xx | A_Type2 | 22 |
xx | xx | B_Type2 | 666 |
xx | xx | C_Type2 | 4444 |
xx | xx | A_Type3 and so on.... | 33 |
now I need it to be:
FIXED COLUMN1 | FIXED COLUMN2 | A_Type | B_Type | C_Type |
xx | xx | Corresponding Values | Corresponding Values | Corresponding Values |
xx | xx | Corresponding Values | Corresponding Values | Corresponding Values |
xx | xx | Corresponding Values | Corresponding Values | Corresponding Values |
xx | xx | Corresponding Values | Corresponding Values | Corresponding Values |
xx | xx | Corresponding Values | Corresponding Values | Corresponding Values |
xx | xx | Corresponding Values | Corresponding Values | Corresponding Values |
xx | xx | Corresponding Values | Corresponding Values | Corresponding Values |
Hi @mjh418
1st Question: yes thats correct as long as I designate the FIXED columns as KEYS, all the rest of them (dynamically) will be selected automatically as data columns no matter how many.
2nd Question: You can do something like this. It a best practice to use recordID tool when transpose to capture rowID.
After tranpose.
1. Using formula to generalize the name.
2. Using crosstab with setting keys as other columns and name is the name and value is value (name and value which came from transpose tool)
Hope this helps : )
Thank you so much, sorry i have more questions.
1. If Those "fixed columns" I mentioned above are actually unique dates, (Start and End date), I can use those just like you used RecordID to get the same results right?
2. Your regex formula is great for getting rid of the number at the end.
This works greatly for nothing to 9. Do you have a more comprehensive formula that gets rid of any numbers?
e.g. some of the columns after the digit 9, will become 9_1, 9_2,,,9_2_1 etc etc.
so after every 9, it will add _1 instead of going to 2 digits.
If there is a formula that can get rid of any numbers at the end, it will be SUPER.
You have helped me so much already..! Thank you very very much for your precious time.