Incremental Load

zied_ahmed1 — Mon, 01 May 2017 09:53:15 GMT

Hello,

I have a QVD and i concatenate data of Today with the other data , my problem is when i load data for two times for example ,data of today will be duplicated, so i need to store data of today in a QVD and do a condition

can any one help me to do thisthe

SET vQvdPath=C:\Users\administrateur.\Desktop\doc\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 dbo."Echeances" ;

NoConcatenate

Plaque_all:

load Distinct Plaque,

'1' as statut_inactif,

Type_Mat as Mat,

date(Today()) as date

resident Fact_Final

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;

NoConcatenate

Flotte2:

load

Plaque,

date,

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

resident Plaque_all

Group by date_flotte,Materiel_flotte,Plaque_flotte,Plaque;

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,

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;

NoConcatenate

FlotteFinal:

LOAD

date_flotte,

Plaque_flotte,

Materiel_flotte,

Taux,Plaque FROM [$(vQvdPath)\QVD_final.qvd] (qvd) ;//source des QVDs historisés

Concatenate

LOAD

* WHERE "date"= Today();

load

//Plaque,

date_flotte,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

Taux,

'ON' as STATUT,

count(Statut_sup) as nbr

resident Flotte4

where Statut_sup='Actif'

group by

date_flotte,

Taux,

//[%Date ID],

Plaque_flotte,

Materiel_flotte;

Concatenate

load

Plaque,

date_flotte,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

'OFF' as STATUT,

count(Statut_sup) as nbr

resident Flotte4

where Statut_sup='Inactif'

group by date_flotte,

Plaque_flotte,

Materiel_flotte,Plaque;

Concatenate

load

date_flotte,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

'Bloquée' as STATUT,

count(Statut_sup) as nbr

resident Flotte4

where Statut_sup='Bloquée'

group by date_flotte,

Plaque_flotte,

Materiel_flotte;

drop table Flotte4;

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

Concatenate

Load*

resident Fact_Final ;

drop table Fact_Final;

Re: Incremental Load

zied_ahmed1 — Mon, 01 May 2017 09:53:37 GMT

rahulpawarb‌

Re: Incremental Load

rahulpawarb — Tue, 02 May 2017 04:38:01 GMT

Hello Zied,

Trust that you are doing good!

If you are getting duplicate records in final table then you can make use of NOT EXISTS function is where clause to tackle with such records.

Refer below links:

QlikView Maven: QlikView Exists Function

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/InterRecordFunctions/Exists.htm

Regards!

Rahul

Re: Incremental Load

zied_ahmed1 — Tue, 02 May 2017 08:14:30 GMT

Hello Rahul,

Thanks for your help,I used NOT EXISTS her :

FlotteFinal:

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

LOAD

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

date_flotte,

Plaque_flotte,

Materiel_flotte,

Taux,

Plaque FROM [$(vQvdPath)\Vectorys_final.qvd] where NOT EXIST(date_flotte) (qvd) ;

I load this and there is no duplicated data just i think the size of the QVD file decreased...is this normal ?

Regards

Re: Incremental Load

rahulpawarb — Tue, 02 May 2017 08:31:09 GMT

Hello Zied,

If you are getting desired data in QVD then there is no need to pay attention towards QVD size (It is decrease because you got rid of duplicate records).

Regards!

Rahul

Re: Incremental Load

zied_ahmed1 — Tue, 02 May 2017 08:35:11 GMT

Thank you Rahul for explications

Regards

topic Re: Incremental Load in Management & Governance

Incremental Load

Re: Incremental Load

Re: Incremental Load

Re: Incremental Load

Re: Incremental Load

Re: Incremental Load