topic Re: Categorizing data in script in App Development

Categorizing data in script

dennisthemenace — Fri, 14 May 2021 09:39:26 GMT

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!

Re: Categorizing data in script

GaryGiles — Fri, 14 May 2021 10:54:24 GMT

For table_Criteria, what is the format of the data in problem_short / problem_description? And are the rules for matching. Is it, "if any word in problem_short or problem_description from table_Criteria matches with a row from table_data, include it or is the rows with the most matches?

Re: Categorizing data in script

dennisthemenace — Fri, 14 May 2021 11:14:10 GMT

Hello Gary,

one field example for table_criteria:

problem_description problem_short relevance

defect Door relevant

field example for table_data:

problem_nr problem_description problem_short

123 Both doors are defect Door

Now, IF problem_Description from table_data INCLUDES (wildmatch) ANY problem_description from table_criteria AND problem_short from table_data INCLUDES (wildmatch) ANY problem_Short from table_criteria THEN match with relevance ("relevant").

Hope, I could explain it...

Re: Categorizing data in script

GaryGiles — Fri, 14 May 2021 11:29:17 GMT

Okay, as long as the problem_description and problem_short fields from table_criteria contain one word per row, we can do something like this:

After loading table_Data and table_Criteria . . .

Join (table_Data)

Load problem_description as problem_description_criteria,

problem_short as problem_short_criteria,

relevance

Resident table_Criteria_Detail;

table_Data_temp:

Load problem_nr,

problem_description,

problem_short,

relevance

Resident table_Data

Where wildmatch(problem_short,'*'&problem_short_criteria&'*')

and wildmatch(problem_description,'*'&problem_description_criteria&'*');

Drop tables table_Data;

Rename table table_Data_temp to table_Data;

Re: Categorizing data in script

dennisthemenace — Fri, 14 May 2021 12:30:11 GMT

Thanks for your quick response, Gary!

In general, it seems to be working, but its recognizing/comparing only the problem_short column in the wildmatch.

So, the output are all problem_nr where problem_short = problem_short_Criteria, but without problem_description = problem_description_criteria

I've found the problem: the rule is not recognizing the problem_description, which stands in the SAME line as problem_short .. which was my fault in rule description... but actually the rule should recognize the combination of problem_short_criteria and problem_description_criteria (being in the same line)

Re: Categorizing data in script

GaryGiles — Fri, 14 May 2021 12:31:05 GMT

They are created and compared the exact same way in the load script, so I'm not sure why it wouldn't work. Did it create the correct outcome for the example data you posted earlier?

Re: Categorizing data in script

dennisthemenace — Fri, 14 May 2021 12:37:53 GMT

Hi Gary,

as I wrote in red, its comparing ANY problem_short with ANY problem_description,

but I would like it like Any problem_short with problem_description from same row number.

So, the combination is important.

Re: Categorizing data in script

GaryGiles — Fri, 14 May 2021 13:09:52 GMT

I still don't follow what you are asking.

Where wildmatch(problem_short,'*'&problem_short_criteria&'*')

and wildmatch(problem_description,'*'&problem_description_criteria&'*');

will give you rows where both problem_short and problem_description criteria.

If you posted a bigger sample data set, it might help.

Re: Categorizing data in script

dennisthemenace — Tue, 18 May 2021 12:09:23 GMT

It's working now fine. Of course its not working as long as the titles in tables are not the same.

Thanks again!

Re: Categorizing data in script

GaryGiles — Tue, 18 May 2021 12:14:39 GMT

Glad it is working. I was working on a response to your deleted post.

One thing I did notice is that you need add a check for the length of problem_short_criteria and problem_description_criteria > 0. A empty field can affect the results of a wildmatch.

Where wildmatch(problem_short,'*'&problem_short_criteria&'*') and len(problem_short_criteria) >0
and wildmatch(problem_description,'*'&problem_description_criteria&'*') and len(problem_description_criteria) >0;

Re: Categorizing data in script

dennisthemenace — Tue, 18 May 2021 12:42:23 GMT

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.

Re: Categorizing data in script

GaryGiles — Tue, 18 May 2021 12:51:34 GMT

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

Re: Categorizing data in script

dennisthemenace — Wed, 19 May 2021 09:08:28 GMT

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?

Re: Categorizing data in script

GaryGiles — Wed, 19 May 2021 11:50:08 GMT

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;

Re: Categorizing data in script

dennisthemenace — Wed, 19 May 2021 12:15:47 GMT

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.

Re: Categorizing data in script

GaryGiles — Wed, 19 May 2021 12:40:01 GMT

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.

Re: Categorizing data in script

dennisthemenace — Wed, 19 May 2021 13:23:47 GMT

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