Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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
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!
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);
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.
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!
Is there another function that works closely to 'exist, but acts more like a wildmatch()?
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!
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
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!