Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
MOTDIR | Motif_CRM | Statut_CRM | Commercial_CRM |
---|---|---|---|
16AUTO1 | autresite | signé | naima |
14AUTO1 | poulet | signé | bach |
MAGIC:
MOTDIR | Motif_MAGIC | Statut_MAGIC | Commercial_MAGIC |
---|---|---|---|
16AUTO1 | autresite | signé | naima |
14AUTO1 | prix | perdu | bach |
Spoc:
MOTDIR | Motif_SPOC | Statut_SPOC | Commercial_SPOC |
---|---|---|---|
16AUTO1 | autresite | signé | naima |
14AUTO1 | prix | perdu | bach |
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
Hello
Does someone can help me?
thank you