Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

OmarBenSalem
Esteemed Contributor

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

19 Replies
MVP
MVP

Re: Help with script !

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?

Highlighted
OmarBenSalem
Esteemed Contributor

Re: Help with script !

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 !

MVP
MVP

Re: Help with script !

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

OmarBenSalem
Esteemed Contributor

Re: Help with script !

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 !

MVP
MVP

Re: Help with script !

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
Esteemed Contributor

Re: Help with script !

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

MVP
MVP

Re: Help with script !

I am going to be looking at this now

OmarBenSalem
Esteemed Contributor

Re: Help with script !

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

MVP
MVP

Re: Help with script !

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

Community Browser