Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

edouard_chandav
New Contributor II

Qlik view to fiabilize

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

(ooxml, embedded labels, table is Feuil1);


MAGIC:

left Join (CRM)

LOAD MOTDIR,

    Motif_MAGIC,

    Statut_MAGIC,

    Commercial_MAGIC

 

FROM

(ooxml, embedded labels, table is Feuil1);



SPOC:

left Join (CRM)

LOAD MOTDIR,

    Motif_SPOC,

    Statut_SPOC,

    Commercial_SPOC

FROM

(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


1 Reply
edouard_chandav
New Contributor II

Re: Qlik view to fiabilize

Hello

Does someone can help me?

thank you

Community Browser