Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have this code and i need to calculate the Taxe per Month:
NoConcatenate
Table2:
load
Plaque,
date,
Materiel,
Plaque as Plaque,
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)) as Taxe
resident Table1
Group by date_flotte,Materiel_flotte,Plaque_flotte,Plaque;
drop table Table1;
Hello Zied,
Below are two probable solutions:
1. Script Level
TaxePerMonth:
LOAD Month(date) AS Month,
Sum(Taxe) AS MonthlyTaxe
Resident Table2
Group By Month(date);
2. Chart Level
-Create a month dimension by adding calculated dimension as - Month(date)
-Add a new expression Sum(Taxe)
Hope this will be helpful.
Regards!
Rahul
Hello Rahul,
When I do this I have invalide expression...
Table2:
load
Plaque,
Month(date) as Month_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
resident Table1
Group by Month(date_flotte),Materiel_flotte,Plaque_flotte,Plaque;
drop table Table1;
Think you should do this
load
Plaque,
Month(date_flotte) as Month_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
resident Table1
Group by Month(date_flotte),Materiel_flotte,Plaque_flotte,Plaque;
drop table Table1;
Hello Zied,
You are referring two different field names in select list and Group By field list.
//Here you are referring date field
Month(date) as Month_date_flotte
//Whereas, here you are referring date_flotte field
Group by Month(date_flotte)
At both the places refer same field to get the desired results.
Regards!
Rahul
Hello,
Yes i just copied it this is the code and gives me invalid expression:
NoConcatenate
Table2:
load
Plaque,
Month(date_flotte) as Month_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 Taxe
resident Table1
Group by Month(date_flotte),Materiel_flotte,Plaque_flotte,Plaque;
drop table Table1;
Hello Zied,
Could you please share the screenshot of error message?
Regards!
Rahul
SET vQvdPath=C:\Users\administrateur.\Desktop\Zied\qvdtest\New; //Path
//2 éme vue qui contient le statut de chaque remorque par jour
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 x.dbo."CG_FLOTTE" ;
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';
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,
Month(date_flotte) as Month_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
resident Plaque_all
Group by Plaque,Month(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;
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:
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] (qvd);//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);
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;
You should have day(date_flotte) in group by clause. If you need to calculate sum(statut_actif)/day(date_flotte).
But that will not give aggregated values at month level.
What exactly you are trying to do here.