I have five closely associated flagfields in a LOAD - they are thematically the same, so I could have put them all into one, but that would have resulted in about 6 nested IF-, AND- and OR functions and a herd of brackets and I like to keep my code nice and clear - so I have to use another RESIDENT LOAD to make them all into one again. No problem there - but instead of checking LEN(TRIM([field])) for every single one (or ISNULL() or anything), could I not use an equivalent to the ALT() function? Is there any equivalent to that for String values? Or do I have to use the DUAL() function to make that work?
personally I think a flag-field should be always numeric (in a boolean logic with 0 and 1) or a dual-field which would be processed always faster as a string and you could use numeric functions like alt() or a range-function or maybe just by multiplying your other fields/expressions with it.
Further I could imagine to use just a single flag-field and query and react on the content within a set analysis - but like always it will depend on the real datamodel and the requirements on it.
IFNoOfRows('Abrechnung_syn_Fehler2') > 0 THEN NoConcatenate Abrechnung_syn_Fehler2_v3: LOAD *, IF((Kat_Pflegefehler_Typ2 = 'LVP' ANDZS_Pflegefehler_Typ2 ='Gefahr' ANDNOT(MATCH(VPTA_Pflegefehler_Typ2,14,0))), 'LV_Zeit_falsch_v1', '') asLV_Zeit_v1, IF((Kat_Pflegefehler_Typ2 = 'LVP' ANDZS_Pflegefehler_Typ2 ='kein ZS' ANDNOT(MATCH(VPTA_Pflegefehler_Typ2,5,0))), 'LV_Zeit_falsch_v2', '') asLV_Zeit_v2, IF((Kat_Pflegefehler_Typ2 = 'LVP' ANDZS_Pflegefehler_Typ2 ='Gefahr' ANDNOT(MATCH(VPTA_Pflegefehler_Typ2,0,17))), 'LV_Zeit_falsch_v3', '') asLV_Zeit_v3, IF((Kat_Pflegefehler_Typ2 = 'LVP' ANDZS_Pflegefehler_Typ2 ='Umpa' ANDNOT(MATCH(VPTA_Pflegefehler_Typ2,0,21))), 'LV_Zeit_falsch_v4', '') asLV_Zeit_v4, IF((Kat_Pflegefehler_Typ2 = 'LVP' ANDZS_Pflegefehler_Typ2 ='kein ZS' ANDNOT(MATCH(VPTA_Pflegefehler_Typ2,0,8))), 'LV_Zeit_falsch_v5', '') asLV_Zeit_v5, Case_Pflegefehler_Typ2asCase_syn, // Wir verknüpfen auch diese kleinere Pflegefehler-Tabelle mit der Haupt-Datentabelle, damit wir später Zugriff auf alle Detaildaten haben. Applymap('Mapping_vorhanden_bei_BMW', Case_Pflegefehler_Typ2, 'N') asvorh_bei_BMW_Fehlertyp2 RESIDENT Abrechnung_syn_Fehler2; DROPTABLE Abrechnung_syn_Fehler2; RENAMETABLE Abrechnung_syn_Fehler2_v3 TO Abrechnung_syn_Fehler2; ENDIF
The logic to this is: In this table, there are three types of items acc. to the field >>Kat_Pflegefehler_Typ2<<: 'LVG', 'LVP' and one other - for these two, there are fixed times - from the class and the info in the field >>ZS_Pflegefehler_Typ2<<, one can definitely calculate what value the field >>VPTA_Pflegefehler_Typ2<< should have - if that is not the case, there is a certain text, otherwise a blank - for every scenario where I can calculate that way, five scenarios in total.
=> The next step would be to make one flagfield out of those five - ah, I think I have it. I don't actually have to distinguish between all those scenarios, every manager should know them - I only have to make one flagfield telling whether the time is correct or not, the remainder can be seen elsewhere.
I think I have it. A listing with LEN(TRIM()) for every one, connected with AND, should do nicely.
Hmmm ... something seems to be wrong there, I have to have a look. The fields don't interact like they're supposed to ...
Ah, I had a scripting error - the scenarios all dealt with the same item_category 'LVP' using different and mutually exclusive queries, they should deal with two different categories - now I have done it all right and it works 😉 What a surprise ...
I know this is a very old post, but in case any one finds there way here one day, the function you want is COALESCE. It's been available in Qlik Sense for a while but it will be a documented function from the Feb 2021 release.