Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone ( stalwar1),
I have an issue I couldn't explain in the title; here it is:
I have a field called Statut flotte (0,1 or 2) and a Num_voyage(0.1.2.3....)
I want to create a new field
statut remorque:
if statut flotte =0 , bloqué
if statut flotte =2 , non active cassée
if statut flotte =1 and num voyage=0 , non actif
if statut flotte =1 and num voyage>0, actif
And I want this new statut remorque to be by month :
+ I want to count the 'nombre_traversée' in the script;
Here is what I've done:
//MyTable:
LOAD
"%Date SEQ",
"%Date ID",
"Calendar date",
"Day of date",
"Week of date",
"Month of date",
"Quarter of date",
"Year of date",
"Year week date",
"Year month date",
"week date",
"quarter date",
"Year quarter date",
"Week start date",
"Week end date",
"Month start date",
..................
FROM [lib://qvd/D_Calendar_28-02-017.qvd]
(qvd);
LOAD
TimeframeId,
Timeframe
FROM [lib://qvd/Timeframe_28-02-017.qvd]
(qvd);
final:
LOAD
month(Date_Dossier) as month_dossier,
"%Date ID",
Code_Activite,
Designation_Activite,
Sens,
Filiale_Creation_Dossier,
"Filiale Origine",
"Pays Fililale Origine",
"Filiale Destination",
Code_Ligne,
Num_Dossier,
Num_Voyage,
Date_Dossier,
Date_Embarquement,
Date_Debarquement,
"Statut Flotte",
Groupe_Complet_Vide,
Plaque,
Parc,
Type_Materiel,
Compagnie,
Remorque_Parc_HorsParc,
Voyage_Ligne,
Code_Carrier,
Pays,
PAYS2,
Charge_Vide
FROM [lib://qvd/final_28-02-017.qvd]
(qvd)
Where Designation_Activite='RORO' and (Parc=('VECTORYS FRANCE') or Parc=('VECTORYS ITALIE'));
What I've done :
tmp1:
Load *, if("Statut Remorque1"=-1, 'bloquée') as "Statut Remorque";
load (month_dossier),"Statut Flotte",Num_Voyage,Plaque,
count(Num_Dossier) as Nombre_Traversée,
sum(DISTINCT if(("Statut Flotte"=0),-1)) as "Statut Remorque1"
Resident final
Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;
////////////////////////////////////////////
Concatenate
Load *, if("Statut Remorque1"=2, 'Non active cassée') as "Statut Remorque";
load (month_dossier),"Statut Flotte",Num_Voyage,Plaque,
count(Num_Dossier) as Nombre_Traversée,
sum(DISTINCT if(("Statut Flotte"=2),2)) as "Statut Remorque1"
Resident final
Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;
/////////////////////////////////////////////////
// Concatenate
// Load *, if("Statut Remorque1"=0, 'Non actif', 'Actif') as "Statut Remorque";
// load (month_dossier),"Statut Flotte",Num_Voyage,Plaque,
// count(Num_Dossier) as Nombre_Traversée,
// sum(DISTINCT if (("Statut Flotte"=1 and Num_Voyage=0 ), 0,
// if (("Statut Flotte"=1 and Num_Voyage>0) ,
// 1))
// ) as "Statut Remorque1"
// Resident final
// Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;
// /////////////////////////////////////////////////
Concatenate
Load *, if("Statut Remorque1"=1, 'Actif') as "Statut Remorque";
load (month_dossier),"Statut Flotte",Num_Voyage,Plaque,
count(Num_Dossier) as Nombre_Traversée,
sum(DISTINCT if (("Statut Flotte"=1 and Num_Voyage>0) ,
1)
) as "Statut Remorque1"
Resident final
Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;
/////////////////////////////////////////////////
Concatenate
Load *, if("Statut Remorque1"=0, 'Non Actif') as "Statut Remorque";
load (month_dossier),"Statut Flotte",Num_Voyage,Plaque,
count(Num_Dossier) as Nombre_Traversée,
sum(DISTINCT if (("Statut Flotte"=1 and Num_Voyage=0) ,
0)
) as "Statut Remorque1"
Resident final
Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;
Left Join (final)
load * Resident tmp1;
Drop Table tmp1;
The result:
bloqué: statut flotte=0
Actif: statut flotte=1 and numvoyage>0 :
Non active cassé: statut flotte =2 :
non active: statut flotte =1 and num voyage>0 : ERROR
What am I doing wrong? and is my approach correct?
Sorry for the long message..
Thank you !
It should be like this:
BY MONTH:
1)Statut Remorque : Actif when : Statut Flotte=1 AND num_voyage>0
2)Statut Remorque : Bloqué when : Statut Flotte=0
3)Statut Remorque :cassé when : Statut Flotte=2
4)Statut Remorque : Non Actif when : Statut Flotte=1 AND num_voyage=0
the first 3, with the script I did were correct but the last condition can't be applied ! Don't know why !
Please, read the messages above to see what errors I got
So, basically if a particular month have all Statut Flotte = 0, it is Bloqué and if a particular month have even a single Statut Flotte = 1 and num_voyage > 0, it is Actif, but if Num_voyage = 0, then Non Actif and so on and so forth....
So does this mean that a single month can have more than one Statut Remorque?
It's by month for the plaques; if it has been actif one time, we say it's actif. and so on
There can be multiple plaque in any given month? Trying to understand the use of Sum(DISTINCT ...) here.... You use Sum(Distinct before using the if statement, is that really necessary?
I tried to use sum without distinct, I had statut_remorque1 = 560 and 499 while I wanted it to be 0,-1,1 etc to use it in the condition above
if statut_remorque1=1 then statut_remorque='actif' etc
if you can alter this and still work out the solution ; I'm flexible to change; it's a test sample
I have to test this before goiing to the client: I always do series of tests to be sure I can do it once on site.
so I'm open to change if a new solution is more efficient
Sorry for late response, but I got busy with other stuff.
I guess my question is, do you have more than one plaque in a month that you need to do this aggregation? Why not just use an if statement in the final table? Why are you looking to group by Month is what I am trying to interpret here so that I can help you better.
Hi Sunny,
Yes, I have more than one plaque in a month.
I want the new statut remorque to be by month (what is the statut of each plaque by month)
I have sent you a connection request, can you send me a private message please?
Done
Hi,
For information, you don't have Num_Voyage = 0 when you filter your final data with Designation_Activite='RORO' and (Parc=('VECTORYS FRANCE') or Parc=('VECTORYS ITALIE')) , also you don't have 'Non Actif' as "Statut Remorque"
Use your condition directly on Where clause like below:
tmp1:
load
(month_dossier),"Statut Flotte",Num_Voyage,Plaque,
count(Num_Dossier) as Nombre_Traversée,
'bloquée' as "Statut Remorque"
Resident final
Where "Statut Flotte"=0
Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;
////////////////////////////////////////////
Concatenate(tmp1)
Load
(month_dossier),"Statut Flotte",Num_Voyage,Plaque,
count(Num_Dossier) as Nombre_Traversée,
'Non active cassée' as "Statut Remorque"
Resident final
where "Statut Flotte"=2
Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;
/////////////////////////////////////////////////
Concatenate(tmp1)
Load
(month_dossier),"Statut Flotte",Num_Voyage,Plaque,
count(Num_Dossier) as Nombre_Traversée,
'Actif' as "Statut Remorque"
Resident final
Where "Statut Flotte"=1 and Num_Voyage>0
Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;
/////////////////////////////////////////////////
Concatenate(tmp1)
Load
(month_dossier),"Statut Flotte",Num_Voyage,Plaque,
count(Num_Dossier) as Nombre_Traversée,
'Non Actif' as "Statut Remorque"
Resident final
Where "Statut Flotte"=1 and Num_Voyage=0
Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;
Left Join(final)
load * Resident tmp1;
Drop Table tmp1;