- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Evaluate field as formula within script
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?
- Tags:
- qlikview_scripting
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'')')