Categorizing data in script

[dennisthemenace]

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!

[dennisthemenace]

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.

[GaryGiles]

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

[dennisthemenace]

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?

[GaryGiles]

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;

[dennisthemenace]

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.

[GaryGiles]

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.

[dennisthemenace]

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