10 Replies Latest reply: Feb 29, 2016 7:16 AM by Giacinto Abbruzzese RSS

    Ifcondition with regard to two or more fields

    Giacinto Abbruzzese

      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

      // [C:\Users\abbruzgi\Documents\Qlik\Sense\Apps\Business Discovery C3\Datenvalidierung\Validation Data_Ergebnis.qvd]

      // (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

        • Re: Ifcondition with regard to two or more fields
          Ruben Marin

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

          • Re: Ifcondition with regard to two or more fields
            Giacinto Abbruzzese

            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
                Ruben Marin

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

                  • Re: Ifcondition with regard to two or more fields
                    Giacinto Abbruzzese

                    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
                        Ruben Marin

                        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

                          • Re: Ifcondition with regard to two or more fields
                            Giacinto Abbruzzese

                            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?

                    • Re: Ifcondition with regard to two or more fields
                      Sangram Reddy

                      Hi Gino,

                       

                      Is it possible to post a sample qvf?

                       

                      Thanks,

                      Sangram.