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

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

Dynamic Processing

Accepted answers

afv2688

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

Dinamyc joiner.yxzp

afv2688

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

All comments

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/td-p/670053?lightbox-message-images-670085=145647i4AC19AC1CFF779CF

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

Hello @rajdaiya ,

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

Dinamyc joiner.yxzp

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.

Dynamic join.yxmd

afv2688

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703