Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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
Regards!
Rahul
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
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
Thank you Rahul for explications
Regards