Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

Help with script !

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

Capture.PNG

Actif: statut flotte=1 and numvoyage>0 :

Capture.PNG

Non active cassé: statut flotte =2 :

Capture.PNG

non active: statut flotte =1 and num voyage>0 : ERROR

Capture.PNG

What am I doing wrong? and is my approach correct?

Sorry for the long message..

Thank you !

19 Replies
OmarBenSalem
Author

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


sunny_talwar

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
Author

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

sunny_talwar

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
Author

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

sunny_talwar

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
Author

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)

sunny_talwar

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

OmarBenSalem
Author

Done

sfatoux72
Partner - Specialist
Partner - Specialist

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;