topic Re: Evaluate field as formula within script in QlikView

Evaluate field as formula within script

winnes — Sat, 16 Nov 2024 21:04:49 GMT

Hello,

I would like to evaluate a string in a field as an expression within the script.

More specifically I have the following table

Stringtable: 
LOAD * INLINE [
    String_to_check, cannot_be_included, must_be_inclued, condition
    'aa bb cd efg', "'*a*','*ef*'", "'*bb*'", "WildMatch(String_to_check,'*a*','*ef*')>0 OR WildMatch(String_to_check,'*bb*')=0"
    'ase', "'*a*','*ef*','*zzz*'", "'*vv*'", "WildMatch(String_to_check,'*a*','*ef*','*zzz*')>0 OR WildMatch(String_to_check,'*vv*')=0"
    'yyy vvv', "'*a*','*ef*','*zzz*'", "'*vv*'", "WildMatch(String_to_check,'*a*','*ef*','*zzz*')>0 OR WildMatch(String_to_check,'*vv*')=0"
    'asd', "'*a*','*ef*','*zzz*'", , "WildMatch(String_to_check,'*a*','*ef*','*zzz*')>0"
    'ase', , "'*vv*'", "WildMatch(String_to_check,'*vv*')=0" 
];

Now I want to have another field column that contains the values 'incorrect' and 'correct' depending on whether the string "String_to_check" hurts the condition in the "condition" column:

LOAD * INLINE [ String_to_check, Result
'aa bb cd efg', 'incorrect'
'ase', 'incorrect'
'yyy vvv', 'correct'
'asd', 'incorrect'
'ase', 'incorrect'
];

It is important that this creation of "Result" is made in the script itself as I need to use this column further later in the script.

I tried using the following code, but evaluate() returns Null-Values as it doesn't detect the condition as a valid formula.

Stringtable_with_Result:
LOAD
	*,
	If(num(evaluate(condition)),'incorrect','correct') 	AS Result
RESIDENT Stringtable;
drop table Stringtable;

Can you help me?

Re: Evaluate field as formula within script

fosuzuki — Thu, 11 Apr 2019 00:26:28 GMT

Assuming you can't change the Stringtable input data - I didn't use the "condition"field...

Stringtable:
LOAD *, recno() as ID;
LOAD * INLINE [
    String_to_check, cannot_be_included, must_be_inclued, condition
    'aa bb cd efg', "'*a*','*ef*'", "'*bb*'", "WildMatch(String_to_check,'*a*','*ef*')>0 OR WildMatch(String_to_check,'*bb*')=0"
    'ase', "'*a*','*ef*','*zzz*'", "'*vv*'", "WildMatch(String_to_check,'*a*','*ef*','*zzz*')>0 OR WildMatch(String_to_check,'*vv*')=0"
    'yyy vvv', "'*a*','*ef*','*zzz*'", "'*vv*'", "WildMatch(String_to_check,'*a*','*ef*','*zzz*')>0 OR WildMatch(String_to_check,'*vv*')=0"
    'asd', "'*a*','*ef*','*zzz*'", , "WildMatch(String_to_check,'*a*','*ef*','*zzz*')>0"
    'ase', , "'*vv*'", "WildMatch(String_to_check,'*vv*')=0" 
];

cannot_be_included:
load ID,
	 sub_cannot_be_included,
	 WildMatch(String_to_check, sub_cannot_be_included) as flag_cannot_be_included;
load ID,
	 String_to_check,
	 PurgeChar(subfield(cannot_be_included, ','), chr(39)) AS sub_cannot_be_included     
Resident Stringtable;

must_be_inclued:
load ID,
	 sub_must_be_inclued,
	 WildMatch(String_to_check, sub_must_be_inclued) as flag_must_be_inclued;
load ID,
	 String_to_check,
	 PurgeChar(subfield(must_be_inclued, ','), chr(39)) AS sub_must_be_inclued
Resident Stringtable;

join(Stringtable)
load ID,
	 Max(flag_cannot_be_included) as flag_cannot_be_included
Resident cannot_be_included
group by ID;

join(Stringtable)
load ID,
	 Max(flag_must_be_inclued) as flag_must_be_inclued
Resident must_be_inclued
group by ID;

drop tables must_be_inclued, cannot_be_included;

join(Stringtable)
load ID,
	 if (not flag_cannot_be_included and flag_must_be_inclued, 'Correct', 'Incorrect') AS Result
resident Stringtable;

Re: Evaluate field as formula within script

winnes — Thu, 11 Apr 2019 08:30:54 GMT

Hello,

I really like your solution and for the case I showed above it works perfectly.
I made a small adaption for the case that the list of phrases that must be included contains more than one word.

Stringtable:
LOAD *, recno() as ID;
LOAD * INLINE [
    String_to_check, cannot_be_included, must_be_included, condition
    'aa bb cd efg', "'*a*','*ef*'", "'*bb*'", "WildMatch(String_to_check,'*a*','*ef*')>0 OR WildMatch(String_to_check,'*bb*')=0"
    'ase', "'*a*','*ef*','*zzz*'", "'*vv*'", "WildMatch(String_to_check,'*a*','*ef*','*zzz*')>0 OR WildMatch(String_to_check,'*vv*')=0"
    'yyy vvv', "'*a*','*ef*','*zzz*'", "'*vv*','*yy*'", "WildMatch(String_to_check,'*a*','*ef*','*zzz*')>0 OR WildMatch(String_to_check,'*vv*')=0 OR WildMatch(String_to_check,'*yy*')=0"
    'yyy vvv', "'*a*','*ef*','*zzz*'", "'*vv*','*zz*'", "WildMatch(String_to_check,'*a*','*ef*','*zzz*')>0 OR WildMatch(String_to_check,'*vv*')=0 OR WildMatch(String_to_check,'*zz*')=0"
    'asd', "'*a*','*ef*','*zzz*'", , "WildMatch(String_to_check,'*a*','*ef*','*zzz*')>0"
    'ase', , "'*vv*'", "WildMatch(String_to_check,'*vv*')=0" 
];

cannot_be_included:
load ID,
	 sub_cannot_be_included,
	 WildMatch(String_to_check, sub_cannot_be_included) as flag_cannot_be_included;
load ID,
	 String_to_check,
	 PurgeChar(subfield(cannot_be_included, ','), chr(39)) AS sub_cannot_be_included     
Resident Stringtable;

must_be_included:
load ID,
	 sub_must_be_included,
	 if(WildMatch(String_to_check, sub_must_be_included),0,1) as flag_must_be_included;
load ID,
	 String_to_check,
	 PurgeChar(subfield(must_be_included, ','), chr(39)) AS sub_must_be_included
Resident Stringtable;

join(Stringtable)
load ID,
	 Max(flag_cannot_be_included) as flag_cannot_be_included
Resident cannot_be_included
group by ID;

join(Stringtable)
load ID,
	 Max(flag_must_be_included) as flag_must_be_included
Resident must_be_included
group by ID;

drop tables must_be_included, cannot_be_included;

join(Stringtable)
load ID,
	 if (not flag_cannot_be_included and not flag_must_be_included, 'Correct', 'Incorrect') AS Result
resident Stringtable;

______________________________________________________

Additional information for users that might have the same problems as I had, I figured out how evaluate() can handle a formula:

So this doesn't work:

evaluate("If(WildMatch(' aa bb cd efg ','*a*','*ef*')>0 OR WildMatch(' aa bb cd efg ','*bb*')=0,'incorrect','correct')")
evaluate([If(WildMatch(' aa bb cd efg ','*a*','*ef*')>0 OR WildMatch(' aa bb cd efg ','*bb*')=0,'incorrect','correct')])
evaluate(´If(WildMatch(' aa bb cd efg ','*a*','*ef*')>0 OR WildMatch(' aa bb cd efg ','*bb*')=0,'incorrect','correct')´)

but this does:
evaluate('If(WildMatch('' aa bb cd efg '',''*a*'',''*ef*'')>0 OR WildMatch('' aa bb cd efg '',''*bb*'')=0,''incorrect'',''correct'')')