Searching for keyword from all fields of the table

[Javier_Loh_HS]

Hi all,

I have a dataset, which has over 20 columns

I'll name some of the fields: Reference No., Customer ID, Amount, Currency, Status, Location, User ID

I also have another excel, Countries.xlsx, which has a column that contains countries like "India", "China", "Vietnam"

Objective:

I want to add all rows in the dataset into a table, that the values in Countries.xlsx appear in.

As long as any of the values in Countries.xlsx is in the row, i want that row to be added to the table.

Yes, i know very likely the values will appear in the "Location" column, but the criteria i was given was for all the fields..

Thanks!

[Arthur_Fong]

Does the column storing the Country field has a specific format?

Eg: Company XYZ (US)

       Company ABC (MY)

       Company BCF (SG)

>> Each row's Country is located in (). 

[Javier_Loh_HS]

Nope not all of them are encased in parenthesis. Some just part of the text too..

[Arthur_Fong]

Try this:

Directory;
Raw:
load Year,Amt,Field1&Field2&Field3 AS Country inline [
Year,Amt,Field1,Field2,Field3
2015,100,XYZ(MY),COM1,COM2
2016,200,COM1,COM2,CN
2018,300,COM3,US,COM4
2019,400,BGCOM4,COM5,COM6
];

Country:
load *,CountryMap as CountryExist inline [
CountryMap
MY,
CN,
US
];
let vRow=NoOfRows('Country');

for i=0 to $(vRow)-1
let vCountry=peek('CountryMap',$(i),'Country');
//NoConcatenate
Data:
load '$(vCountry)' as Country,Year,Amt,'$(i)'+1 as New resident Raw
//
where
//exists(CountryExist,Country)and
WildMatch(Country,'*$(vCountry)*');
next i

drop table Raw;

Update the inline file with your production data.

Thanks and regards,

Arthur Fong

[Arthur_Fong · Accepted solution]

Updated the script to display all fields which is mapped to the Country table:

Directory;
Raw:
load Year,Amt,Field1&Field2&Field3 AS Country,Field1,Field2,Field3 inline [
Year,Amt,Field1,Field2,Field3
2015,100,XYZ(MY),COM1,COM2
2016,200,COM1,COM2,CN
2018,300,COM3,US,COM4
2019,400,BGCOM4,COM5,COM6
];

Country:
load *,CountryMap as CountryExist inline [
CountryMap
MY,
CN,
US
];
let vRow=NoOfRows('Country');

for i=0 to $(vRow)-1
let vCountry=peek('CountryMap',$(i),'Country');
//NoConcatenate
Data:
load '$(vCountry)' as Country,Field1,Field2,Field3,Year,Amt,'$(i)'+1 as New resident Raw
//
where
//exists(CountryExist,Country)and
WildMatch(Country,'*$(vCountry)*');
next i

drop table Raw;
exit Script;

[Javier_Loh_HS]

Huge thanks!! Truly the MVP. It works wonderfully.. just need to start writing scripts for all 20 tables.