need no duplication data QVD

zied_ahmed1 — Mon, 08 May 2017 15:46:51 GMT

Hello, I have this script and would like when i reload data i will not have a duplicated value

SET vQvdPath=C:\Users\administrateur.\Desktop\Zied\qvdtest\New; //Path

Flotte:

LOAD Plate as Plaque_flotte,

date(Today()) as date_flotte,

TrailerType as Materiel_flotte,

ParkName as Parc_flotte,

SATUT as stat_flotte

;

SELECT Plate,

TrailerType,

ParkName,

SATUT

FROM SOFTNETREPORT.dbo."CG_FLOTTE_Suivi_Echeances" where ParkName in ('ITALIE' , 'FRANCE' ) ;

NoConcatenate

Plaque_all:

load Distinct Plaque,

'1' as statut_inactif,

Type_Materiel as Materiel_flotte,

date(Today()) as date_flotte

resident Fact_Final Where NOT Exists(Plaque_flotte,Plaque) and Type_Materiel<>'DiversMateriel' ;

//Concatenation statut inactif récupérer de la table plaque_all avec les autres statuts

Concatenate

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;

//somme des differents statuts

NoConcatenate

Flotte2:

load

Plaque,

date_flotte ,

Materiel_flotte,

Plaque_flotte as Plaque_flotte,

sum(statut_endom) as statut_endom,

sum(statut_actif) as statut_actif,

sum(statut_inactif) as statut_inactif,

sum(statut_bloque) as statut_bloque,

// (sum(statut_actif)/day(date_flotte)) as Taux

(sum(statut_actif)/ Day(MonthEnd(date_flotte)) ) as Taux

resident Plaque_all

Group by Plaque,date_flotte,Materiel_flotte,Plaque_flotte;

drop table Plaque_all;

//Condition pour savoir le statut du remorque

NoConcatenate

Flotte3:

load

Taux,

Plaque,

statut_inactif,

statut_bloque,

statut_endom,

date_flotte,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

if (statut_actif>statut_bloque and statut_actif>statut_inactif,1,

(if(statut_bloque>statut_actif and statut_bloque>statut_inactif,2,

(IF(statut_inactif>statut_actif and statut_inactif>statut_bloque,3))))) as Statut_cond,

statut_actif

Resident Flotte2;

drop table Flotte2;

//dégagement du statut (final) du remorque

NoConcatenate

Flotte4:

load

Plaque,

statut_actif,

date_flotte,

statut_inactif,

statut_bloque,

statut_endom,

//Statut_sup,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

Taux,

if (Statut_cond=1,'Actif',

(if (Statut_cond=2,'Bloquée',

(IF(Statut_cond=3,'Inactif'))))) as Statut_sup

Resident Flotte3;

drop table Flotte3;

//pour SR sans traversée

NoConcatenate

FlotteFinal:

//chargement de QVD qui contient les données des jours qui précedent

LOAD

autonumber( date(date_flotte, 'DD/MM/YYYY'),'%Date ID') as [%Date ID],

date_flotte,

Plaque_flotte,

Materiel_flotte,

Taux,

Plaque FROM [$(vQvdPath)\QVD_final.qvd] (qvd) where date_flotte>'30/04/2017';

//and not Exists(date_flotte) ;//source des QVDs historisés

Concatenate

//Concatenation du QVD avec les données d'ajourd'hui

LOAD

* WHERE "date_flotte"= Today();

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

FlotteFinal:

load

Statut_sup,

date_flotte,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

Taux,

Pick(Wildmatch(Statut_sup,'Actif','Inactif','Bloquée'),'ON','OFF','Bloquée') as STATUT,

count(Statut_sup) as nbr

resident Flotte4

group by

date_flotte,

Plaque_flotte,

Materiel_flotte,

Statut_sup,

Taux;

drop table Flotte4;

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

Concatenate

Load*

resident Fact_Final ;

drop table Fact_Final;

Re: need no duplication data QVD

zied_ahmed1 — Tue, 09 May 2017 08:23:12 GMT

rahulpawarb‌

stalwar1‌

Re: need no duplication data QVD

prieper — Tue, 09 May 2017 08:34:27 GMT

LOAD ..... WHERE NOT EXISTS (KeyField)

should do

Peter

Re: need no duplication data QVD

zied_ahmed1 — Tue, 09 May 2017 10:21:14 GMT

i do where not EXIST (date_flotte) it doesn't work

topic Re: need no duplication data QVD in Management & Governance

need no duplication data QVD

Re: need no duplication data QVD

Re: need no duplication data QVD

Re: need no duplication data QVD