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

Transform a set expression to an expression in the script

Hi all (stalwar1‌),

Here is my demand,

I have a list of dates (date_embarquement), and a list of plaques (plaque):

for each date and plaque: we should only have ONE code_carrier.

In the source, we could find for Code_Ligne = MAR, in a date_embarquement and a plaque something like this:

Capture.PNG

which is false since It's the same folder (dossier).

Here's the original expression:  count({<Code_Ligne={'MAR'}>}Dossier)

I changed it like this:

count({<Code_Ligne={'MAR'},

Dossier={"=Count(Aggr((Code_Carrier&Plaque),Date_Embarquement,Plaque))=1"}>}Dossier)

and the result is: (I think It's a success):

Capture.PNG

Now, the question is; how to implement the previous expression in the script to force it to only bring one code_carrier per date_embarqueent and Plaque for code ligne maroc?

The script is as follow:

LOAD

   Dossier,

  

   "%Date ID",

    Code_Activite,

    Designation_Activite,

    Sens,

    Filiale_Creation_Dossier,

    "Filiale Origine",

    "Pays Fililale Origine",

    "Filiale Destination",

    Code_Ligne,

   

    Num_Voyage,

    Date_Dossier,

    Date_Embarquement,

    Date_Debarquement,

    Groupe_Complet_Vide,

    Plaque,

    Parc,

    Type_Materiel,

    Compagnie,

    Remorque_Parc_HorsParc,

    Voyage_Ligne,

    Code_Carrier,

    Pays,

    PAYS2,

    Charge_Vide

FROM [lib://qvd/final_16-03-017.qvd]

(qvd)

Where Code_Ligne = 'MAR';

You'll find attached the 2 Qvds I'm working with : (calendar and final)

Thank you,

29 Replies
OmarBenSalem
Author

I understand !

Now can you please explain to me :

Count({<$(vSetPeriodPrev), Code_Ligne={'MAR'}, Groupe_Complet_Vide = {c,C,G},

  Dossier = {"=Count({<$(vSetPeriodPrev)>}Aggr(Only({<$(vSetPeriodPrev)>} Code_Carrier&Plaque),Date_Embarquement,Plaque))=1"}>} Dossier)



why It has to be 2 calls of the vSetPeriodPrev?

What does every one do?

sunny_talwar

Since you are ignoring selections, and setting your own date range, you have to do that within the inner and outer aggr() functions. That's all it is doing here.... I don't know what else to say

OmarBenSalem
Author

Hi stalwar1‌,

I've found yet another 'problem'

In our expression, this what we're doing:

pres:

count({<$(vSetPeriodPres),Code_Ligne={'MAR'},Groupe_Complet_Vide={V},

Dossier={"=Count(Aggr((Code_Carrier&Plaque),Date_Embarquement,Plaque))=1"}>}Dossier)

prev:

Count({<$(vSetPeriodPrev), Code_Ligne={'MAR'}, Groupe_Complet_Vide = {V},

Dossier = {"=Count({<$(vSetPeriodPrev)>}Aggr(Only({<$(vSetPeriodPrev)>} Code_Carrier&Plaque),Date_Embarquement,Plaque))=1"}>} Dossier)

Like this: pres 2016 = prev 2017 (which is the correct thing)

Now, if we change our 2 expressions and instead work with :

Groupe_Complet_Vide={c,C,G}

we'll find differences btwn pres 2016 and prev 2017 (for example)

Can you please see it with me?

Thanks

sunny_talwar

I am not sure I understand brother... can you point out by showing images?

OmarBenSalem
Author

See: when I choose 2016, the prev expression (with group)={c,C,G} :

Capture.PNG

Now, when I choose 2015, the pres expression should be 580; but it's 585 :

Capture.PNG

As I said, this problem occurs when in my expressions I work with :

Groupe_Complet_Vide={c,C,G}



When I work with Groupe_Complet_Vide={V}, I don't have this problem

ps: I already changed my script as follow:

Maroc:

LOAD

"%Date ID",

    Code_Activite,

    Designation_Activite,

Date_Embarquement,

   

    Plaque,

....

    Remorque_Parc_HorsParc,

    Voyage_Ligne,

  Pays,

    PAYS2,

    Charge_Vide

FROM [lib://qvd/final_16-03-017.qvd]

(qvd)

Where Code_Ligne = 'MAR' 

;

Left Join(Maroc)

LOAD

  

    Date_Embarquement,

   

    Plaque,

  

  Only(Code_Carrier) as code

FROM [lib://qvd/final_16-03-017.qvd]

(qvd)

Where Code_Ligne = 'MAR' 

Group by Date_Embarquement,Plaque

;

Concatenate

NoMaroc:

LOAD

"%Date ID",

    Code_Activite,

    Designation_Activite,

    Sens,

    Filiale_Creation_Dossier,

...

    Remorque_Parc_HorsParc,

    Voyage_Ligne,

  Pays,

    PAYS2,

    Charge_Vide

FROM [lib://qvd/final_16-03-017.qvd]

(qvd)

Where Code_Ligne <> 'MAR' 

;

And my expression is now becoming really simple : count({<$(vSetPeriodPrev),Code_Carrier={'MAR'}>}Dossier)

But, I'm just curious to know why is there a problem with the Groupe_Complet_Vide={c,C,G}

You can see it since you ahev the app in hand

Hope that was rather clear?

sunny_talwar

Not completely understand your data well enough to say much (unless I decide to dig deep into it ), but can you check if this makes any difference

Count({<$(vSetPeriodPrev), Code_Ligne={'MAR'}, Groupe_Complet_Vide = {c,C,G},

Dossier = {"=Count({<$(vSetPeriodPrev), Code_Ligne={'MAR'}, Groupe_Complet_Vide = {c,C,G}>}Aggr(Only({<$(vSetPeriodPrev), Code_Ligne={'MAR'}, Groupe_Complet_Vide = {c,C,G}>} Code_Carrier&Plaque),Date_Embarquement,Plaque))=1"}>} Dossier)

If this doesn't do anything, I will check your app at a later point and see what might be causing it (digging deeper part)

Best,

Sunny

OmarBenSalem
Author

No, that didn't change a thing

The prev value is always wrong; as if it's deleting the rows where there is dossier (by date and plaque) existing in 2 different carriers.

Pres value for C,G is right; the prev is not..

Even in the script

sunny_talwar

So script solution isn't working as well?

OmarBenSalem
Author

Same result; there is always a problem with C,G: in fact it's deleting the dossier that is duplicated in 2 code_carrier by date/plaque  not counting only one dossier

Have you remarked the difference betwen the pres and prev values in the app?

sunny_talwar

Not yet... I will look at it sometime today... but in the mean time can you try to explain in simple English what exactly are you trying to do?