topic Designer - Lookup of field from other data source in Alteryx Designer Desktop Discussions

Designer - Lookup of field from other data source

LadyNaureen — Tue, 25 May 2021 10:30:07 GMT

Dear Community Members

I need your expert advice on following:

A CSV data file say STUDENT is being used as INPUT and we have to perform a transformation on COURSE_ID column of this file and add new column COURSE_DESC using this field.

The transformation logic is that there is a separate COURSE CSV, which has COURSE_ID and COURSE_DESC columns.

We tried to use FORMULA control on COURSE_ID field but it is not giving option lookup and adding a new field.

Can you please help us to resolve this blocker for us.

Thank you.

Regards,

LadyN

Re: Designer - Lookup of field from other data source

mceleavey — Tue, 25 May 2021 10:32:56 GMT

Hi @LadyNaureen ,

Can you post your data or mock data so we can help?

Re: Designer - Lookup of field from other data source

mceleavey — Tue, 25 May 2021 10:52:32 GMT

Hi @LadyNaureen ,

the answer is the join tool, using the COURSE_ID field as the key on which to join.

If you post your data I can build it for you.

You can click HERE to read about the join tool.

Re: Designer - Lookup of field from other data source

LadyNaureen — Tue, 25 May 2021 11:00:27 GMT

Two files attached.

Re: Designer - Lookup of field from other data source

mceleavey — Tue, 25 May 2021 11:06:21 GMT

Hi @LadyNaureen ,

thanks for that.

I've attached the workflow for you.

The input tool needs to be ";" separated:

Hope this helps.

Re: Designer - Lookup of field from other data source

LadyNaureen — Tue, 25 May 2021 11:30:56 GMT

Thank you for prompt resolution mceleavey

I have used the JOIN and could see the OUTER JOIN icon but on clicking it, nothing is happening. The concern is if COURSE ID is not found in COURSE file then record should not disappear but should be there with NULL COUSER_DESC.

Re: Designer - Lookup of field from other data source

atcodedog05 — Tue, 25 May 2021 11:56:57 GMT

Hi @LadyNaureen

Based on your scenario, my suggestion would be to use Find&Replace tool which works like excel (vlookup i,e does a left outer join.)

There resource on it : https://community.alteryx.com/t5/Interactive-Lessons/VLookUps-with-Designer/ta-p/80201

hope this helps 🙂

Re: Designer - Lookup of field from other data source

LadyNaureen — Mon, 31 May 2021 11:24:38 GMT

Thank you all for prompt response and resolution.

This is really a great community 🙂

Regards,

LadyN

Re: Designer - Lookup of field from other data source

atcodedog05 — Mon, 31 May 2021 11:35:12 GMT

Hi @LadyNaureen

Hope your issue get resolved 🙂