Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
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')
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,
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
I see the same conditions, just changing 'Fehler' to 'Error'
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?
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
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?
Hi Gino,
Is it possible to post a sample qvf?
Thanks,
Sangram.
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