1 Reply Latest reply: Mar 20, 2018 6:57 AM by Edouard Chandavoine RSS

    Qlik view to fiabilize

    Edouard Chandavoine

      Hello Community,

       

      It's my first time asking for a little help, I have tried to be as clear as possible

      I want to use Qlikview to check the integrity between several database, 3 of them called CRM/MAGIC/SPOC.


      I successfully did an example with 3 fields

      So I am creating 3 check field:

      Check_Motif taking the value OK if it's the same value in all systems NOT OK if there is a missmatch

      Check_Statut same

      Check_Commercial same


      I will have a lot of fields so I would like to know if I can automate the creation of the 'check_field'

      I am thinking about a loop to automaticaly create the 'check_field  and filling them with yes or no.


      Here is my try but no idea on how to code this:

      (in Excel language it would be something like)

      For Each field = to '%_CRM'

      if (Left(Field, search("_",Field)-1)&"_Magic"=   Left(Field, search("_",Field)-1)&"_SPOC", if(Left(Field, search("_",Field)-1)&"_Magic"=Left(Field, search("_",Field)-1)&"_CRM",'OK','NOT OK') as 'check_&Left(Field, search("_",Field)-1)




      Below what I sucessfully did with no automation


      CRM:

       

      MOTDIRMotif_CRMStatut_CRMCommercial_CRM
      16AUTO1autresitesignénaima
      14AUTO1pouletsignébach



        MAGIC:

       

      MOTDIRMotif_MAGICStatut_MAGICCommercial_MAGIC
      16AUTO1autresitesignénaima
      14AUTO1prixperdubach


        Spoc:

      MOTDIRMotif_SPOCStatut_SPOCCommercial_SPOC
      16AUTO1autresitesignénaima
      14AUTO1prixperdubach


       

      CRM:

      LOAD MOTDIR,

          Motif_CRM,

          Statut_CRM,

          Commercial_CRM

      FROM

      [C:\Users\e\Desktop\Datasources\TEST INTEGRITY\CRM.xlsx]

      (ooxml, embedded labels, table is Feuil1);


      MAGIC:

      left Join (CRM)

      LOAD MOTDIR,

          Motif_MAGIC,

          Statut_MAGIC,

          Commercial_MAGIC

       

      FROM

      [C:\Users\e\Desktop\Datasources\TEST INTEGRITY\MAGIC.xlsx]

      (ooxml, embedded labels, table is Feuil1);



      SPOC:

      left Join (CRM)

      LOAD MOTDIR,

          Motif_SPOC,

          Statut_SPOC,

          Commercial_SPOC

      FROM

      [C:\Users\e\Desktop\Datasources\TEST INTEGRITY\Spoc.xlsx]

      (ooxml, embedded labels, table is Feuil1);



      Tablecheck:

      Load *,


      if (Motif_MAGIC=Motif_SPOC, if (Motif_MAGIC=Motif_CRM ,'OK','NOT OK') )as check_motif,

      if (Statut_MAGIC=Statut_SPOC, if (Statut_MAGIC=Statut_CRM ,'OK','NOT OK') )as check_statut,

      if (Commercial_MAGIC=Commercial_SPOC, if (Commercial_MAGIC=Commercial_CRM ,'OK','NOT OK') )as check_commercial


      Resident CRM;


      DROP TABLE CRM;


      Expected result

      Capture.PNG