Dynamic replacement

[sweeneki]

Hello,

I have a Core table with fields: Key1, Key2, A, B, C, D, ...

I have multiple 'update' files with varying fields that all are present on the Core table, all with one of several key fields that are also present on the Core table. Some examples: 

Example 1: Key1, C,D,X

Example 2: Key2, A, B, J, K, Z

Each update file has a list of values under the field names that need updating in the Core table where the Key of the Update file corresponds to one of the keys in the Core table.

I can import the various update files with wild cards; I have imported the Core table too. 

How can I automatically 'map' the given key field and the variable number of fields/columns in the Update files to update to their corresponding field/column names in the Core table?

Ideally I would like to save and mark as 'superseded' the original record in the Core table and append the updated record marked as 'new' and adding the file name of the relevant update table.

Many thanks.

Kieran

[dfurlow]

probably would be useful to see an example of your core table and a typical update.   Might be useful to look at the Dynamic Rename tool or to consider joining on position, if the structure of every update is consistent and includes all fields, even if they are empty.  Weekly challenge number 50 may offer a similar sounding situation.   https://community.alteryx.com/t5/Weekly-Challenge/Challenge-50-Preparing-Survey-Data/td-p/40312   The challenge output is not so useful, but a number of the solutions use the Dynamic Rename or/and joins to accomplish what i think you are describing.  Good luck!

[apathetichell]

Without seeing your data - transpose and join sound like the key items here... Can you post some sample data and what you want?

[sweeneki]

I've attached a sample per my question above. The idea is each of my clients receives a file to which they can append, amend or delete rows from the original Core file. Each action has a separate tab in the 'Input_YYYYQ#_*.xlsx files. I would have preferred to included several keys given that there are many subsystems feeding my Core file and each my need a specific key. 

I now receive the error: Warning: Input Data (1): The file "\\Ubsprod.msad.ubs.net\groupshares\GLOBAL\LIBOR_MIGRATION_MI\LIBOR_MIGRATION_MI\Disclosure\Alteryx\Input_2021Q3_B.xlsx|||`Update$`" has a different schema than the 1st file in the set and will be skipped

Both have the same schema!

CSV does work, but then my clients would need to work with 3 separate files which is unsatisfactory. 

I took dfurlow's suggestion and included all columns A-M (I would have preferred only to include those columns that have changed given the large number of columns as user will need to navigate). 

I looked at a macro but don't understand how it would help/use it: https://community.alteryx.com/t5/tkb/articleprintpage/tkb-id/knowledgebase/article-id/694

Many thanks in advance.

Kieran

[apathetichell]

core fields are the same - but they don't have the same schema... you can tell by looking at the columns which have all null()s in the differing files.