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 !

1 Solution

Accepted Solutions
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;

View solution in original post

19 Replies
sunny_talwar

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?

OmarBenSalem
Author

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 !

sunny_talwar

Would you be able to share 5-10 lines of data and share the expected output from those rows?

OmarBenSalem
Author

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 !

sunny_talwar

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 )

OmarBenSalem
Author

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)

:

Capture.PNG

sunny_talwar

I am going to be looking at this now

OmarBenSalem
Author

Excuse my impatience; but, have you understood what I'm willing to do?

sunny_talwar

I am attaching an Excel export from your app... can you point out what is not right here?