Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
i prepared some data to explain my problem:
My goal is to determine how many vehicles where active on each day. (day >= tour start && day <= tour end)
For each day the number of active vehicles should look like this:
I tried to do this in Qlik with the following expression:
Sum(aggr(Sum(If(Dates >= [tour start] AND Dates <= [tour end],1,0)),vehicle))
That gives me a total of 17 active vehicles. (Too much)
I know what the formula is doing wrong but I dont know how to fix it.
In this case:
'A' should only count as one active vehicle for 02.01.2021. But my formula counts 'A' twice.
Does anyone know, how to adjust the formula.
I think I should add something like
count(distinct( vehicle + Date))
Thanks in advance 🙂
hi,
try this script
vehiv:
LOAD
Datee,
Vehicle,
"start date",
"end date"
FROM [lib://REPORT EXTRACTION SSSS1.xlsx]
(ooxml, embedded labels, table is vehiv);
RESULT:
Load Distinct
(Vehicle&'-'&SDate) as DDTT,
SDate,
Vehicle;
LOAD
Vehicle,
Date(Date([start date]+iterno()-1),'DD-MM-YYYY') as SDate
resident vehiv while [start date]+IterNo()-1 <=[end date];
drop table vehiv;
Result:
3hrs to reach.
Ksrinivasan
Move your logic in Script.. Create 'Active Flag' field.. Try to avoid Aggr() as much as possible.
Code for reference only
//Add calculated field in script
If(Dates >= [tour start] and Dates <= [tour end],1,0) as Active_Flag
//IN expression
count(distinct {< Active_Flag = {1} >} vehicle)
hi,
try this script
vehiv:
LOAD
Datee,
Vehicle,
"start date",
"end date"
FROM [lib://REPORT EXTRACTION SSSS1.xlsx]
(ooxml, embedded labels, table is vehiv);
RESULT:
Load Distinct
(Vehicle&'-'&SDate) as DDTT,
SDate,
Vehicle;
LOAD
Vehicle,
Date(Date([start date]+iterno()-1),'DD-MM-YYYY') as SDate
resident vehiv while [start date]+IterNo()-1 <=[end date];
drop table vehiv;
Result:
3hrs to reach.
Ksrinivasan
Thank You so much.
I really appreciate your work and effort 🙂
you are welcome Mr. schuffe.
ksrinivasan
Hello again 🙂
I got another question. I think it is very simple but I'm not aible to do it the right way. How would this script look like if the connection isn't an excel file anymore ? My App is now connected to an oracle server and I changed the script like this:
vehiv:
LOAD
[Datee],
[Vehicle],
[start date],
[end date];
SELECT
"Datee",
"Vehicle",
"start date",
"end date"
FROM "..."."table_name";
RESULT:
Load Distinct
(Vehicle&'-'&SDate) as DDTT,
SDate,
Vehicle;
LOAD
Vehicle,
Date(Date([start date]+iterno()-1),'DD-MM-YYYY') as SDate
resident vehiv while [start date]+IterNo()-1 <=[end date];
drop table vehiv;