Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Dynamic replacement

sweeneki
5 - Atom

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

4 REPLIES 4
dfurlow
8 - Asteroid

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
18 - Pollux

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

sweeneki
5 - Atom

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
18 - Pollux

2021-08-02.pngcore 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.

Labels