Equivalent of ALT() function for STRING values?

[datanibbler]

Hi,

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?

Thanks a lot!

Best regards,

DataNibbler

[MK_QSL]

Can you provide sample app for this.?

[marcus_sommer]

Hi DataNibbler,

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.

- Marcus

[datanibbler]

Hi Manish,

the entire app would be a bit much - and mostly irrelevant to this - but I can post that particular piece of code here:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

IF NoOfRows('Abrechnung_syn_Fehler2') > 0 THEN
NoConcatenate
Abrechnung_syn_Fehler2_v3:
LOAD
*,
IF((Kat_Pflegefehler_Typ2 = 'LVP' AND ZS_Pflegefehler_Typ2 ='Gefahr' AND NOT(MATCH(VPTA_Pflegefehler_Typ2,14,0))), 'LV_Zeit_falsch_v1', '') as LV_Zeit_v1,
IF((Kat_Pflegefehler_Typ2 = 'LVP' AND ZS_Pflegefehler_Typ2 ='kein ZS' AND NOT(MATCH(VPTA_Pflegefehler_Typ2,5,0))), 'LV_Zeit_falsch_v2', '') as LV_Zeit_v2,
IF((Kat_Pflegefehler_Typ2 = 'LVP' AND ZS_Pflegefehler_Typ2 ='Gefahr'  AND NOT(MATCH(VPTA_Pflegefehler_Typ2,0,17))), 'LV_Zeit_falsch_v3', '') as LV_Zeit_v3,
IF((Kat_Pflegefehler_Typ2 = 'LVP' AND ZS_Pflegefehler_Typ2 ='Umpa'  AND NOT(MATCH(VPTA_Pflegefehler_Typ2,0,21))), 'LV_Zeit_falsch_v4', '') as LV_Zeit_v4,
IF((Kat_Pflegefehler_Typ2 = 'LVP' AND ZS_Pflegefehler_Typ2 ='kein ZS' AND NOT(MATCH(VPTA_Pflegefehler_Typ2,0,8))), 'LV_Zeit_falsch_v5', '') as LV_Zeit_v5,
Case_Pflegefehler_Typ2 as Case_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') as vorh_bei_BMW_Fehlertyp2
RESIDENT Abrechnung_syn_Fehler2;
DROP TABLE Abrechnung_syn_Fehler2;
RENAME TABLE 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.

Best regards,

DataNibbler

[datanibbler]

Hi Marcus,

you're right. Numerical flag_fields have many advantages. I will have a second look, I go in two steps anyway, so a text in the first step is not necessary as it will be deleted anyway.

[datanibbler]

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 ...

[AlexOmetis]

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. 

[clarisa]

Thank you, I was looking for this function! 🙂