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 !
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;
What is nombre_traversée? I am not 100% sure I understand the logic of what you goal is... but may be you need to use Where clause instead of if statement right before you concatenate them into a single table. Again, I am not 100% sure I understand. Would you be able to share a sample?
Nombre traversé" = count(num dossier) for parc = italie and france and activité roro:
Since, I'll be only using these in my dashboard, I calculated it in the script instead of a set expression
The problem now is in the NON ACTIVE clause, all other 3 if statements return an accurate result, but non active is false !
Would you be able to share 5-10 lines of data and share the expected output from those rows?
You'll find attached some test QVDs and the test app I'm working with:
You can recreate the script; as I told you, I want to create a new field 'statut remorque' (by month)
the new field depends of num_voyage and statut flotte (the conditions I've written above)
I also tried doing this :
Tmp_Actif:
Load Num_Dossier,"Statut Flotte",Parc,Designation_Activite,Type_Materiel,Date_Dossier,count(Num_Dossier) as Nombre_Traversée
Resident final_tmp
where "Statut Flotte"=1
and match(Parc,'VECTORYS ITALIE','VECTORYS FRANCE')
and Designation_Activite= 'RORO'
group by Date_Dossier,"Statut Flotte",Designation_Activite,Parc, Num_Dossier,Type_Materiel;
// Load Type_Materiel,Designation_Activite,Parc,Date_Dossier,count(Plaque) as nombre_plaque
// Resident final_tmp
// group by Type_Materiel,Designation_Activite,Parc,Date_Dossier;
Tmp2:
Load * ,
if (Nombre_Traversée>0 , 'Actif','Non Actif') as Statut_Remorque
Resident Tmp_Actif;
Concatenate(Tmp2)
Load * ,'Bloquée' as Statut_Remorque
Resident final_tmp where "Statut Flotte"=0;
concatenate(Tmp2)
load *, 'Non actif cassé' as Statut_Remorque
Resident final_tmp where "Statut Flotte"=2;
drop table Tmp_Actif;
drop table final_tmp;
drop table COM_BI_Dossier_Cat;
But with no succes !
I am leaving for work, will try this at work once I reach (if nobody else is able to help you out in the mean time )
Note stalwar1 , that even though I'm using this script:
Concatenate
Load *, if("Statut Remorque1"=5, '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<1) ,
5)
) as "Statut Remorque1"
Resident final
Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;
the statut remorque does not take 5 as a value for non actif , it always take 0 and is joined to all statut flotte (0,1,2)
:
I am going to be looking at this now
Excuse my impatience; but, have you understood what I'm willing to do?
I am attaching an Excel export from your app... can you point out what is not right here?