How to check if a column exists in a table & dynamically lookup value from another table

[rajdaiya]

Hi Alteryx community, Im fairly new to Alteryx and have been stuck on a particular problem for a while now.

Consider an example:

Table 1 consists of these columns

And Table 2 consists of these columns:

Constraint: Both these column names are generated dynamically when a workflow runs and these column names keep changing every time I run the workflow for a different dataset for a different timeperiod.

My question is how to lookup values for Table 2 from Table 1 (ie if column name exists copy+paste values for respective dates) else keep null. Please help me out? I would really appreciate it.

So I am looking for a solution which checks CIN, SOC and TV from Table 2 have corresponding values in Table 1 for those dates, and fetches those numbers and populates in table 2. Since VOD and PART dont exist in Table 1, keep them null or 0

[cmcclellan]

Your constraint makes it very difficult/impossible to do this consistently.

The quick answer is to join the 2 tables together, but if the column names keep changing then you can't even do that 😞 

[Nada__Alkhalaf]

HI, 

I think you should look into this post. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Join-two-tables-without-common-field/t...

[Christina_H]

You say the column names are generated dynamically - is there logic to the names?  Could you use a dynamic rename tool to give them standard names so that you can use a join tool?

[afv2688 · Accepted solution]

Hello @rajdaiya ,

Interesting question. Was able to produce a dynamic joiner which would update and take always the values from the left table and update the right table. I use in this case the date as the joiner and take all the data from the table 2 together with the joined one (right join basically)

You can add extra columns on the first formula tools to check it out but as far as I tested it works out 🙂

Hope it helps

Regards

[Christina_H]

I think this is actually a lot simpler than it sounds.  Transpose both datasets, right join on date and name, then cross-tab back to the original orientation.

[afv2688 · Accepted solution]

Hello @Christina_H ,

love your simple solution 🙂 thought of that one too but had bad experiences in the past while doing transpose and crosstab with large datasets, takes forever to process the info.

So following my own advice on this topic would propose a faster solution to my own previous one:

I just replaced the transpose with field info tools since I don't need the rest of the info.

Regards

[rajdaiya]

Thank you everyone for the help! I was able to resolve this. Sincerely appreciate it