CompleX Lookup wherein Blanks are ignored and also an approximate match

[RounakGupta]

Hi All, There are a set of data set wherein I need to bring in the comments . Steps to perform is as below: Use Exact matches for columns A-C ignoring blanks where ever possible. while for column D , if there exist similar character match in lookup table as D in the dataset, then it is match only then the comments from lookup tables needs to be brought in For example Headers for main data set is as below

Z	A	B	C	D

While the Lookup tables looks something like below

Exact	Exact	Exact	Contains	Values to bring in Main data
A	B	C	D	Comments
XX		YY		A Category
	XX		XX XX XXXX	B Category
YYY		ZY	AA AP DASD

I need to bring in the comments to the Main data set using lookup in Col A-D. The D column is not an exact match but could have the relevant characters/words/strings in the main data set. In First Row , the lookup should try and match A and C and then bring in the Comments. In 2nd Row It should Match exactly the Column B value, while D's value could be lookup basis -if it contains these characters in D Column of main dataset , then a match.. Finally in 3rd data , B should be ignored, A & C should have exact match in main data. while D column value in lookup table should match with the one in main dataset . If it matches , then C category should be filled in. Note here 3 conditions have been shown but there are around 28 comments category and lookup columns are around 8-9 with 2 of them should be an approximate match.

How could I create a lookup given the above conditions. Has anyone used this type of case earlier?

[danilang]

Hi @RounakGupta 

Can you provide some sample input data and what the output would look like?

Thanks

Dan

[RounakGupta]

Hi Dan, Thanks for coming back.

The second data table above is a lookup table. All I need is to build a logic to program in such a way that if it reads the first line, it should identify the blanks and ignore those columns. and then proceed to next set. In the first option, A & C column is relevant column, it should go into the main data , create a lookup using A & C (which should be identical name in the main data) and create join, update Comments in the main data and segregate the joined data. For the unjoined, it would go back again into the lookup table, see which columns are relevant for join, (in the above scenario , it is B&D). Here D column is an approximate match so the join should be made in such a way that it joins column B as usual join, while D as an approximate match and then find out which matches the criteria and update the comments .

So wherever columns is an exact match, it should do a usual join, the header name in the data and the lookup should be normal join. While  in columns where it mentions "Contains" that column should be used to join dataset to do an approximate join. Only if these conditions satisfy, will you add the comments into your main data while keep the rest as blanks.

Apologies for not saving the data as I cannot think of a different way to showcase it. If I prepare some random data , I would share it with you.. Cant share the original data