LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: sree

Hi Team,

My source File is xlxs file. in one of the column values are like below

location
-------
DE
FR
AN
DEN
DE
AN
DE
AN
AN
BA
AN
India
IL
IN
America
Aruba.


In other file columns,value are like this.



Country 3digit code Country Name Nationality
------- ----------------- ------------ -----------
AF AFG Afghanistan Afghan
AX ALA Aland Islands Alandish
AL ALB Albania Albanian
DZ DZA Algeria Algerian
AS ASM American Samoan
VI VIR American Islands
AD AND Andorra Andorran
AO AGO Angola Angolan
AI AIA
AG ATG
AR ARG
AM ARM
AW ABW

Requirement :
In source file , for location columns values 2, few records 4 or full country name, not in one format country name.
But while loading this location column records, we need to load location country name in 3 digits country code(whatever format in source file/column) by doing lookup/using any node with the second file/second file column records.
Please help me how can we can achieve that .
Thanks in advance for all your support.

Regards,
Sree.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ltolleson

Sree,

None of the data in your source at the top matches any of the fields from the data in the bottom input, which makes it difficult to know what you expect as output. Could you please give us some data that would match between the two inputs and also what the output should look like.


Thanks,
Larry

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: sree

Originally posted by: ltolleson
					

Sree,


None of the data in your source at the top matches any of the fields from the data in the bottom input, which makes it difficult to know what you expect as output.  Could you please give us some data that would match between the two inputs and also what the output should look like.



Thanks,

Larry
			

Hi Larry,
Sorry for the confusion in sample data.

Here is the sample data ,
from source file

location
-------

IN
BR
AN
AFG
ARM
India
IND

Data from other file for lookup /any other use purpose.

Country Code 3 Chars Country Name Nationality
------------ --------- --------------- -------------
AF AFG Afghanistan Afghan
AR ARG Argentina Argentinian
IN IND India Indian
IN IND India Indian
BR BRA Brazil Brazilian

Output requires as :


Country Name
---------------
AFG
ARG
IND
IND
BRA

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

Interesting challenge. Using multiple Lookup nodes, one for each of Country, Code 3 Chars, Country Name, and Nationality may be the easiest thing to do and to understand.

But, to do it with one Lookup node, maybe for optimization purposes, you could take that lookup data and convert it to two columns, one lookup value and one result, and then use that. Your lookup data would be like this.

Name Value
--------- ---------
AF AFG
AR ARG
IN IND
IN IND
BR BRA
AFG AFG
ARG ARG
IND IND
IND IND
BRA BRA
Afghanistan AFG
Argentina ARG
India IND
India IND
Brazil BRA
Afghan AFG
Argentinian ARG
Indian IND
Indian IND
Brazilian BRA

After writing the nodes that convert to Excel Lookup data to this format I'd run it through either a duplicate detection node or a unique sort because the likelihood of having duplicate lookup values is too high to ignore, and any duplicates with contradictory values should be corrected.