Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gino2780
Contributor

Ifcondition with regard to two or more fields

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 1Header 2
ABRE E OrtPotenziale seitens NL
(Leer) (means missing, no cell entry)(Leer) (means missing, no cell entry)

BG

ja
GAnein
GLunbekannt
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

1 Solution

Accepted Solutions

Re: Ifcondition with regard to two or more fields

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')

10 Replies

Re: Ifcondition with regard to two or more fields

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
Contributor

Re: Ifcondition with regard to two or more fields

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
Contributor

Re: Ifcondition with regard to two or more fields

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

Re: Ifcondition with regard to two or more fields

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

gino2780
Contributor

Re: Ifcondition with regard to two or more fields

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?

Re: Ifcondition with regard to two or more fields

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
Contributor

Re: Ifcondition with regard to two or more fields

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?

reddys310
Honored Contributor II

Re: Ifcondition with regard to two or more fields

Hi Gino,

Is it possible to post a sample qvf?

Thanks,

Sangram.

Re: Ifcondition with regard to two or more fields

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