Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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):
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,
Same thing; no result yet..
Sorry to keep relying on you to correct my expression !
It's getting too complicated
Can you share you app, without rar-ing it
Done (first page, last KPI (right)
Do you know why most of the things are not visible to me?
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)
What is the field name? I don't think I can change the object type. I will recreate the filter object
Timeframe
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)
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 !
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