Ifcondition with regard to two or more fields

[gino2780]

Hello Qlik community,

here is what i've done so far to give you a sneak peek before i raise my question:

I'm working on implementing specific validation rules into a data-quality-management-app for my data.

The source data is an excel file, which comes from a sharepoint list.

The validation rules should check the denoted fields, or more specific inconsistent excel cells contents and write it to a .csv data-file.

Here are some examples of the script of validation rules which i've already implemented:

Validierung:

LOAD

Konzernnummer,

// Feld muss numerisch sein

if(isnum(Konzernnummer),'ok','Fehler') as Konzernnummer_alphanum,

// Feldlänge muss 7 stellig sein und numerisch

if(isnum(Konzernnummer) and len(Konzernnummer)=7,'ok','Fehler') as Konzernnummer_Länge,

// Validierung Feld darf nicht leer sein

if(Konzernnummer<>NULL(),'ok','Fehler') as Konzernnummer_leer,

// Feld muss numerisch sein

'EXCEL' as SourceSystem,

RowNo() as Zeilennummer,

today() as ExtractionDate,

FileName() as Dateiname   

FROM [lib://DQM/LG Liste.xlsx]

(ooxml, embedded labels, table is owssvr);

// Export Ergebnis als qvd und txt

Store Validierung into [lib://DQM/LG Liste Data_Ergebnis.qvd](qvd);

// FROM

//

// (qvd);

Store Validierung into [lib://DQM/LG Liste Data_Ergebnis.csv](txt);

// Store Validierung2 into [lib://DQM/LG Liste Data_Ergebnis2.csv](txt);

// Ergebnisdatei wird mit Tagesdatum versehen (im Dateinamen) - wird somit nicht überschrieben

Store Validierung into [lib://DQM/LG Liste Data_Ergebnis_$(vDatum).qvd](qvd);

Store Validierung into [lib://DQM/LG Liste Data_Ergebnis_$(vDatum).csv](txt);

Now i'm looking forward to another validation rule, that include if conditions ('and' | 'or' combinations) with regard to two fields.

The logic expression should look like this:

If Potenziale seitens NL = ja, then ABRE E Ort ≠ KG | BG

If Potenziale seitens NL ≠ ja, then ABRE E Ort = KG | BG

Header 1	Header 2
ABRE E Ort	Potenziale seitens NL
(Leer) (means missing, no cell entry)	(Leer) (means missing, no cell entry)
BG	ja
GA	nein
GL	unbekannt
KG
KK
L
nicht vorhanden
NL
S
SK

As output i'd like to write cases of combinations into my validation

Nachricht geändert durch Giacinto Abbruzzese

[rubenmarin · Accepted solution]

Not sure to understand, you're looking for this?:

If Potenziale seitens NL = ja, then ABRE E Ort ≠ KG | BG

If([Potenziale seitens NL]='ja' and [ABRE E Ort]<>'KG' and [ABRE E Ort]<>'BG', 'Ok', 'Fehler')

If Potenziale seitens NL ≠ ja, then ABRE E Ort = KG | BG

If([Potenziale seitens NL]<>'ja' and ([ABRE E Ort]='KG' or [ABRE E Ort]='BG'), 'Ok', 'Fehler')

[gino2780]

Thanks, it worked well. I've just added a flag to be able to store the the result of the validation rule into a new field for the output validation file.csv

If([Potenziale seitens NL]='ja' and [ABRE E Ort]<>'KG' and [ABRE E Ort]<>'BG', 'Ok', 'Fehler') as Potenziale1,

[gino2780]

Hello again,

although the thread is a little bit old, i've noticed that the last script command (which 've marked as 'right answer') wasn't what i have been looking for.

Instead of the two lines

If([Potenziale seitens NL]='ja' and [ABRE E Ort]<>'KG' and [ABRE E Ort]<>'BG', 'Ok', 'Fehler') as newfield1

If([Potenziale seitens NL]<>'ja' and ([ABRE E Ort]='KG' or [ABRE E Ort]='BG'), 'Ok', 'Fehler') as newfield2

i need a script syntax that can check:

If ([Potenziale seitens NL] = ja, then [ABRE E Ort] shall not be = KG | BG) as newfield1

                If condition is fullfilled, then show OK in newfield1

                If the condition is not fulfilled, then show Error in newfield 1

If ([Potenziale seitens NL] ≠ ja, then [ABRE E Ort] shall be = KG | BG) as newfield2

                If condition is fullfilled, then show OK in newfield2

                If the condition is not fulfilled, then show Error in newfield2

[rubenmarin]

I see the same conditions, just changing 'Fehler' to 'Error'

[gino2780]

Well, actually you are right. Sorry for the confusion.

1.) I was just wondering why you have chosen the and-operator for the first statement : ...[ABRE E Ort]<>'KG' and [ABRE E Ort]<>'BG'...

where as in the second statement you've chosen the or-operator:

...([ABRE E Ort]='KG' or [ABRE E Ort]='BG')...

?

2.) Why didn't you set brackets for the first statement ...[ABRE E Ort]<>'KG' and [ABRE E Ort]<>'BG'...,

but did set brackets for the second statement ...([ABRE E Ort]='KG' or [ABRE E Ort]='BG')...

I've figured that it makes no difference (2.) whether you set the brackets for one of the statements, for both or none, right?

[rubenmarin]

Your first statement says "then [ABRE E Ort] shall not be = KG | BG)"

this is translated as [ABRE E Ort]<>'KG' and [ABRE E Ort]<>'BG' (both must be accomplished to fullfil the condition), note here i'm searching to accomplish "<>" (not equal)

The second says "then [ABRE E Ort] shall be = KG | BG)

this is translated as [ABRE E Ort]='KG' or [ABRE E Ort]='BG' (any of both must be accomplished), Note here are looking for "=" (equal), the brackets are to set priority of the 'or' part higher than the 'and'

The first sentence has all 'and' sentences so doesn't needs to give priority to any part over other.

Without brackets the 'and' has higher priority and QV will try something like:

If(([Potenziale seitens NL]<>'ja' and [ABRE E Ort]='KG') or [ABRE E Ort]='BG', 'Ok', 'Fehler')

So a record with [ABRE E Ort]='BG' will return true even if [Potenziale seitens NL]='ja', because the 2nd part of the 'or' is accomplished

[gino2780]

Ruben, thank you for your deep insights on this topic again. I really appreciate it.

I did the logical mistake to put the | (or-operator) between KG, BG (mutually exclusive categories of field ABRE E Ort) in the first statement.

I should have written for the first statement:

If Potenziale seitens NL = ja, then ABRE E Ort ≠ KG

&

If Potenziale seitens NL = ja, then ABRE E Ort ≠ BG

instead of:

If Potenziale seitens NL = ja, then ABRE E Ort ≠ KG | BG

E.g.:

If Potenziale seitens NL = ja, then ABRE E Ort ≠ KG & BG

would have been wrong too, because the categories of ABRE E Ort (see Table above; Header 1 for ABRE E Ort) are mutually exclusive. Agree?

[reddy-s]

Hi Gino,

Is it possible to post a sample qvf?

Thanks,

Sangram.

[rubenmarin]

Hi gino, I see that logically has the same result expressions.

I attach a sample with all combinations of your table above, check if any of the rows is getting a wrong result