topic Re: solution for script in Management & Governance

solution for script

zied_ahmed1 — Wed, 19 Apr 2017 20:06:23 GMT

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;

Re: solution for script

zied_ahmed1 — Wed, 19 Apr 2017 20:12:43 GMT

rahulpawarb‌

Re: solution for script

rahulpawarb — Thu, 20 Apr 2017 04:00:49 GMT

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

Re: solution for script

zied_ahmed1 — Thu, 20 Apr 2017 05:47:44 GMT

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...

Re: solution for script

rahulpawarb — Thu, 20 Apr 2017 06:22:36 GMT

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

Re: solution for script

zied_ahmed1 — Thu, 20 Apr 2017 06:55:23 GMT

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

Re: solution for script

rahulpawarb — Fri, 21 Apr 2017 03:47:19 GMT

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

Re: solution for script

zied_ahmed1 — Fri, 21 Apr 2017 08:29:20 GMT

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