Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Javier_Loh_HS
New Contributor II

Searching for keyword from all fields of the table

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!

Labels (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: Searching for keyword from all fields of the table

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

MC.PNG

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;

View solution in original post

14 Replies
Partner
Partner

Re: Searching for keyword from all fields of the table

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
New Contributor II

Re: Searching for keyword from all fields of the table

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!

Partner
Partner

Re: Searching for keyword from all fields of the table

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);

 

MVP
MVP

Re: Searching for keyword from all fields of the table

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
New Contributor II

Re: Searching for keyword from all fields of the table

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
New Contributor II

Re: Searching for keyword from all fields of the table

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

Javier_Loh_HS
New Contributor II

Re: Searching for keyword from all fields of the table

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!

Partner
Partner

Re: Searching for keyword from all fields of the table

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
New Contributor II

Re: Searching for keyword from all fields of the table

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!