Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
my aim is to load a table with problem issues and add a column to categorize each row/data with the help of certain criterias if it's relevant or not.
To make it easier to understand, here is an example of given data:
table_Data:
problem_nr;
problem_description;
problem_short;
table_Criterias:
problem_description;
problem_short;
relevance;
table_Final:
problem_nr;
...
relevance;
The problem is, that the colums problem_short / problem_description are not 100 % identical. The table criteras includes only key words, where the table_data includes more complex descriptions. So, there is no applyMap possible. Any idea, how to solve it the easiest way?
Thanks and kind regards!
Hi Gary, I was just facing the problem as you posted! Its not workin so far..
I am trying to figure out the following:
table_Data_temp:
Load problem_nr,
problem_description,
problem_short,
relevance
Resident table_Data
Where
IF(problem_description_criteria = ''
,
wildmatch(problem_short,'*'&problem_short_criteria&'*')
,
wildmatch(problem_short,'*'&problem_short_criteria&'*')
and
wildmatch(problem_description,'*'&problem_description_criteria&'*')
);
So, if the criteria list has no definition for "description_criteria", then only wildmatching problem_short.
But its also not working yet.
You will need to use an OR condition in your where clause, like this (check that the field names are correct):
Where (wildmatch(problem_short,'*'&problem_short_criteria&'*') and len(problem_short_criteria) >0
and wildmatch(problem_description,'*'&problem_description_criteria&'*') and len(problem_short_criteria) >0) or
(len(problem_description_criteria) = 0 and wildmatch(problem_short,'*'&problem_short_criteria&'*')
and len(problem_short_criteria) >0);
Now I tried to filter all problem_nr where I got no defined criteria with:
Load
...
'please check' as relevance
Where
IsNull(
(wildmatch(problem_short,'*'&problem_short_criteria&'*') and len(problem_short_criteria) >0
and wildmatch(problem_description,'*'&problem_description_criteria&'*') and len(problem_short_criteria) >0) or
(len(problem_description_criteria) = 0 and wildmatch(problem_short,'*'&problem_short_criteria&'*')
and len(problem_short_criteria) >0);
)
But it doesnt seem to be working. Any further idea how to do that process?
I reworked the load script a little. You may need to change fields names to match yours. I've attached an image of the results I am getting.
table_Criteria_Detail:
Load problem_description, problem_short, relevance inline [
problem_description, problem_short, relevance
defect, door, n. relevant
defect, window, n. relevant
,door,n. relevant
];
table_Data:
Load problem_description, problem_short, problem_nr inline [
problem_description, problem_short, problem_nr
both door defect, door, 11
defect, door, 22
new, window, 33
repared, door, 44
];
NoConcatenate
table_Data_temp:
Load *
Resident table_Data;
Join (table_Data_temp)
Load problem_description as problem_description_criteria,
problem_short as problem_short_criteria,
relevance
Resident table_Criteria_Detail;
join (table_Data)
Load problem_nr,
relevance
Resident table_Data_temp
Where (wildmatch(problem_short,'*'&problem_short_criteria&'*') and len(problem_short_criteria) >0
and wildmatch(problem_description,'*'&problem_description_criteria&'*') and len(problem_short_criteria) >0) or
(len(problem_description_criteria) = 0 and wildmatch(problem_short,'*'&problem_short_criteria&'*')
and len(problem_short_criteria) >0);
Drop tables table_Data_temp,table_Criteria_Detail;
Thanks a lot!
After I am getting the right results, I want to filter all problem_nr where I didnt get a result (in case that this problem is not defined in table critera).. this rest of problem_nr will get 'please check' in the column relevance.
So I was joining the tables ID_Alle (all problem_nr) with ID_geprüft (problem nr that are prooven/n. relevant) and made a
where IsNull(Wildmatch(ID_Alle,[ID_geprüft]))
but there is no output again.
Having a hard time following exactly what you are trying to do, but a couple of observations:
The WildMatch() function returns 0 when the isn't a match. I'm not sure why you are using encasing the Wildmatch with IsNull(). Are you trying get Wildmatch(ID_Alle,[ID_geprüft]) = 0 ?
Second, if you are trying to find instances where ID_Alle is a substring of [ID_geprüft], it would be:
Wildmatch([ID_geprüft],'*'&ID_Alle&'*') = 0
Hope that helps.
Hi Gary, I am sorry. I tried many ways, but it's not working. When I am using '=0' as you mentioned already, its giving me all IDs as an output. Actually, it should be filtering just a handful IDs, for instance "6666"as it is unlike all given ID_geprüft.
//separate load of all possible IDs from table data
table_data_IDs:
LOAD
"QM ID" as ID_Alle
FROM [lib:/....xlsx]
;
join(table_data_IDs)
//joining all IDs having a relevance status from the 'wildmatch procedure' before
Table_temp_checked:
Load
ID as ID_geprüft
Resident table data_checked;
//filtering all IDs_Alle <> ID_geprüft as ID_Rest
NoConcatenate
Table_temp_4:
Load
ID_Alle as ID_Rest
resident table_data_IDs
where Wildmatch([ID_geprüft],'*'&ID_Alle&'*')=0