Dynamic Multi Field Value Replacement

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 : )

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 : )

Question 1: Yes thats correct. You can do that.

Question 2: You can use formula like below

REGEX_Replace([Name2], "(.+\u)[\d_]+", "$1")

Hope this helps : )

All comments

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.

Qiu

@mjh418
I believe a sample data set for input and output is necessary here.

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)

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

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.

Question 1: Yes thats correct. You can do that.

Question 2: You can use formula like below

REGEX_Replace([Name2], "(.+\u)[\d_]+", "$1")

Hope this helps : )