solution for script

[zied_ahmed1]

I would like to use the result of the table Flotte_plaque in the condition in red and use a condition like if(stat_flotte='ACTIF',1,0) as statut_actif,(something like

if exist(plaque)...as statut INACTIF

////*************set the qvd folder path************

SET vQvdPath=C:\Users\administrateur.VECTORYS\Desktop\Zied\qvdtest\\Qvdflotte;

// LOAD * FROM (qvd) ;//source des QVDs historisés

//Concatenate:

Flotte:

LOAD

    * WHERE  "date_flotte"= Today();

LOAD Plate as Plaque_flotte,

Today() as date_flotte,

TrailerType as Materiel_flotte,

  FrDes as Description,

  ParkName as Parc_flotte,

  DateDebutBlocage,

  NoteBlocage,

  SATUT as stat_flotte;

SELECT Plate,

TrailerType,

  FrDes,

  ParkName,

  DateDebutBlocage,

  NoteBlocage,

  SATUT

FROM Suivi_Echeances;

// ////*********store QVD**************

STORE [Flotte] into [$(vQvdPath)\Flotte.qvd](qvd);

Flotte_plaque:

Load

Plaque_flotte Resident Flotte;

Left Join (Flotte_plaque)

outer join(Fact_Final)

load Distinct Plaque

resident Fact_Final Where NOT Exists(Plaque_flotte,Plaque);

Flotte1:

Load

   Plaque_flotte ,

   Materiel_flotte,

   date_flotte,

  if(stat_flotte='ACTIF',1,0) as statut_actif,

     if(stat_flotte='INACTIF',1,0)  as statut_endom,

        if(stat_flotte='BLOQUE',1,0)  as statut_bloque

    Resident Flotte;

    drop table Flotte;

[zied_ahmed1]

rahulpawarb‌

[rahulpawarb]

Hello Zied,

Trust that you are doing well!

I considered that table Flotte_plaque & Flotte1 shares Plaque_flotte common field (linkage between two). As a workaround I have used Mapping Load feature & ApplyMap function of QlikView (refer below given sample script):

Flotte_plaque:
LOAD Plaque_flotte,
     if(stat_flotte='ACTIF',1,0) as statut_actif,
     if(stat_flotte='INACTIF',1,0)  as statut_endom,
     if(stat_flotte='BLOQUE',1,0)  as statut_bloque
Resident Flotte;
Left Join (Flotte_plaque)
outer join(Fact_Final)
LOAD Distinct Plaque 
resident Fact_Final
Where NOT Exists(Plaque_flotte,Plaque);


//Mapping load to generate statut_actif
Map_Flotte_plaque_statut_actif:
MAPPING LOAD 
     Plaque_flotte,
     statut_actif
Resident Flotte_plaque;


//Mapping load to generate statut_endom
Map_Flotte_plaque_statut_endom:
MAPPING LOAD
     Plaque_flotte,
     statut_endom
Resident Flotte_plaque;


//Mapping load to generate statut_bloque
Map_Flotte_plaque_statut_bloque:
MAPPING LOAD
     Plaque_flotte,
     statut_bloque
Resident Flotte_plaque;

Flotte1:
LOAD
   Plaque_flotte ,
   Materiel_flotte,
   date_flotte,
   //consume above defined mapping loads
   ApplyMap('Map_Flotte_plaque_statut_actif', Plaque_flotte, 0) AS statut_actif,
   ApplyMap('Map_Flotte_plaque_statut_endom', Plaque_flotte, 0) AS statut_endom,
   ApplyMap('Map_Flotte_plaque_statut_bloque', Plaque_flotte, 0) AS statut_bloque
Resident Flotte;


DROP TABLE Flotte;

Hope this will be of help.

Regards!

Rahul

[zied_ahmed1]

Hello Rahul...how i can use the the result of this :

Left Join (Flotte_plaque)

outer join(Fact_Final)

LOAD Distinct Plaque

resident Fact_Final

Where NOT Exists(Plaque_flotte,Plaque);

I need condition like the stat_endom and stat_actif for the list of Plaque...

[rahulpawarb]

Hello Zied,

Could you please elaborate more on this? Also share the application file. This will help us to understand the problem and provide you expected inputs.

Regards!

Rahul

[zied_ahmed1]

Hello Rahul,

My problem is :

I have 2 tables: first table have the field Plaque (have all Plaques)

second table have the field Plaque_Flotte who have just a part of Plaques...so i use where exist to have the no common plaques (the difference) after that i need to affect these plaques to a statut like the stat_actif and the other stat in the next table

[rahulpawarb]

Hello Zied,

I understood, if you have two tables where you want to get a status field from one table into another table. You can achieve the same using Mapping Load functionality (refer above given examples).

Please correct me if I misinterpreted (Also share the your application).

Regards!

Rahul

[zied_ahmed1 · Accepted solution]

Hello Rahul,

Thanks for your help,

But i have found a solution i added a field '1' as statut_inactif in plaque_all and I concatenate it with the table where the other statut

Best regards