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.
SOLVED

Lookup value in a table range and return unique value

bohanxia
5 - Atom

Hi,

 

I would like to get some help to translate Excel "LOOKUP (Lookup value, Lookup vector, result vector) into Alteryx workflow.

 

I have 2 data sources. One contains 148 records which each record has a "flow date". The second data source is a maturity bucket mapping. "flow date" in data source 1 should be used here to decide which maturity bucket it falls under. For example below, a record with flow date 17 Oct should return maturity bucket ">overnight up to 2 days" and bucket ID "30".

 

Also as per below, any records with flow date between 15 Dec 2019 and 13 Jan 2020 should return bucket ID "180" and records with flow date beyond 14 Jan 2020 should return bucket ID "190".

 

I have pasted a screenshot of my Alteryx workflow below, hope it helps to clarify too. Is there any way I can build this workflow to assign buckets for the 148 records?

 

clipboard_image_0.png

 

Final results from 1st data source with "flow date":

clipboard_image_1.png

 

Alteryx workflow, top is 1st data source, bottom is 2nd data source with maturity bucket mapping

clipboard_image_2.png

 

Load maturity bucket mapping from Excel to Alteryx:

clipboard_image_3.png

 

Thank you!

4 REPLIES 4
RolandSchubert
16 - Nebula
16 - Nebula

Hi @bohanxia ,

 

I think, you have to "translate" the lookup ranges (From date - To date) to single rows ("date"), each date in the interval From-To represented by one row. You can use the Generate Rows tool to achieve this (new field of type DATE, start is "From date", end is "To date", formula is DateTimeAdd([NewDateField], 1, "days"). You can use the new field to join file 1 and file 2 on [Flow Date] and [NewDateField]. Hope this is helpful.

 

Best regards

 

Roland

bohanxia
5 - Atom

Thanks very much your approach worked!

Ye_Olde_Baller
6 - Meteoroid

Any chance yo can share your workflow once you solved your issue?  Thank you!

bohanxia
5 - Atom

hi sure,

 

after loading the dates mapping:

bohanxia_0-1572020562212.png

 

Generate rows:

bohanxia_1-1572020598648.png

 

Join the 2 data by dates, lookup flow date in the dates mappings and append the maturity bucket and IDs to the rows.

bohanxia_2-1572020654156.png

 

Hope this helps.

Labels