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]

Try this sample script:

Directory;
Raw:
load * inline [
Year,Amt,Country
2015,100,MY
2016,200,CN
2018,300,US
2019,400,BG
];

Country:
load *,Country as CountryExist inline [
Country
MY,
CN,
US
];

NoConcatenate
Data:
load * resident Raw
where exists(CountryExist,Country);
drop table Raw;

Update the sample table with your production data.

Thanks and regards,

Arthur Fong

[Javier_Loh_HS]

Hi,

Thanks for the prompt response! From my understanding, where exists(CountryExist,Country);, only checks within the Countries and CountryExist field right?

I need a way to search through not just one field, but all of them. Do let me know if i'm wrong!

Thanks!

[Arthur_Fong]

You can add in more conditions like below:

NoConcatenate
Data:
load * resident Raw
where exists(CountryExist,Country)

and exists(CityExist,City)

and exists(LocationExists,Location);

[tresesco]

One way could be (though bit costly in terms of performance) like:

Create composite key taking all fields, then cross join the country table with the main table, now filter the table using wildmatch() in where clause; check if the composite key has string containing country.

[Javier_Loh_HS]

Thanks for the response, once again! I'll work on it soon, and let you know if I stumble on another roadblock! Also, I do believe Tresesco's idea would work haha. Only problem is I have a total of 12 tables with over 20 columns each.. Thanks!

[Javier_Loh_HS]

Is there another function that works closely to 'exist, but acts more like a wildmatch()?

[Javier_Loh_HS]

Hi this works perfect with Exist() but because there are some fields that contain not only the country value.. is there a way to use WildMatch() in this case? My code works up till the wildmatch section..

Directory;
Raw:
load * inline [
Year,Amt,Country
2015,100,CN
2016,200,CN
2017,300,US
2019,400,US4
];

Country:
load *,
Country_Name as CountryExist

FROM ....;

NoConcatenate
Data:
load * resident Raw
where exists(CountryExist,Country)
or exists(CountryExist,Amt)
or WildMatch(CountryExist,'*'&Year&'*');
drop table Raw;

Thanks!

[Arthur_Fong]

What are the fields that contain not only Country value?

Can you give an example?

I see your wildmatch is matching country with year.

Do you mean in Country field, there are rows for example like:

MY2019

SG2018

[Javier_Loh_HS]

Yes! Like there is a field e.g. Company Name: Company XYZ (US)
Sorry i can't give you the exact name because of privacy issues!

Edit: Main point is that some fields contain the country value as well! So im trying to add in a wildmatch() to properly search through the values, however I am stuck upon attempting the wildmatch() function.

Thanks!