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

Same thing; no result yet..

Sorry to keep relying on you to correct my expression !

It's getting too complicated

sunny_talwar

Can you share you app, without rar-ing it

OmarBenSalem
Author

Done (first page, last KPI (right)

sunny_talwar

Do you know why most of the things are not visible to me?

Capture.PNG

OmarBenSalem
Author

Extensions:

the one on the right are buttons;

the one up left is list (you can convert it to filter object and then select a timeframe)

sunny_talwar

What is the field name? I don't think I can change the object type. I will recreate the filter object

OmarBenSalem
Author

Timeframe

sunny_talwar

Changed your variable to this (Basically remove the double quotes around the Max(Year of date)... part)

[Calendar date]=,[Month of date]=,[Year of date]={$(=max([Year of date])-$1)}

and then this expression

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)

OmarBenSalem
Author

That worked like a charm but why !

I really can't figure out the use of the " " when I work with an aggr function in set analysis !

Sometimes, it only works with double quotes; especially in the case of x= {">=sthing <=sthingelse" }

In this case it works for me only if I surround it with double quotes

But it's the contrary thing in our case !

It's really confusing !!

Well, thank you very mich sunny !

sunny_talwar

The problem is that you have double quotes surrounding double quotes

If I expand your variable within your expression... it will look like this

Count({<$(vSetPeriodPrev), Code_Ligne={'MAR'}, Groupe_Complet_Vide = {c,C,G}, Dossier = {"=Count({<[Calendar date]=,[Month of date]=,[Year of date]={"$(=max([Year of date])-$1)"}>} Aggr(Only({<[Calendar date]=, [Month of date]=, [Year of date] = {"$(=max([Year of date])-$"}>} Code_Carrier&Plaque), Date_Embarquement,Plaque)) = 1"}>} Dossier)

Now this is confusing for QlikView to understand... you can use Escape sequences to handle this, but in your case, since you were doing a set analysis on number, removing double quotes also worked.... but if in case you cannot remove them, then you will have to either use Escape sequence or another technique to handle this