Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
omarbensalem
Esteemed Contributor

Re: Help with script !

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


MVP
MVP

Re: Help with script !

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?

omarbensalem
Esteemed Contributor

Re: Help with script !

It's by month for the plaques; if it has been actif one time, we say it's actif. and so on

MVP
MVP

Re: Help with script !

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?

omarbensalem
Esteemed Contributor

Re: Help with script !

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

MVP
MVP

Re: Help with script !

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.

omarbensalem
Esteemed Contributor

Re: Help with script !

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)

MVP
MVP

Re: Help with script !

I have sent you a connection request, can you send me a private message please?

omarbensalem
Esteemed Contributor

Re: Help with script !

Done

sfatoux72
Valued Contributor

Re: Help with script !

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;