From accumulate to increments

[ITSPA]

Hi, I have a table with the following fields

Truck#, date, Odometer

In this table there is a record for each day, truck and the total kilometrs (Odometer)...

The question is, how can I know hoy many km each truck covers each day?

Approch 1:

Km day5 = Odometer day 5 - Odometer day 4

How can I do this in Qlik?

Approach 2:

Some days the driver don't record the ODO info, so I don't have the info...

km day 8 = (Odo day 8 - Odo day 5) / (day 8 - day 5)

How can I do this in Qlik?

[sidhiq91]

@ITSPA  Could you please provide us sample data that would help us to understand more and help to resolve the issue?

[ITSPA]

Of course Sidhiq91

Truck	Date	Odo
1	01-Jul-2022	2450
2	01-Jul-2022	65400
1	02-Jul-2022	2650
2	02-Jul-2022	66400
1	03-Jul-2022	2950
1	04-Jul-2022	3800
2	06-Jul-2022	70100
1	07-Jul-2022	6050
2	07-Jul-2022	71000

[ITSPA]

And we want to have a new field Km/day

Formula of km/day:

km day 8 = (Odo day 8 - Odo day 5) / (day 8 - day 5)

For example -> Truck: 2 - date: 06-Jul-2022

(Odo day 06-Jul -Odo day 02-Jul) / (day 06-Jul - day 02-Jul) = Km day 06-Jul

(70100 - 66400) / (6 - 2) =  3700 / 4 = 925 = km day 06-Jul

Truck	Date	Odo	km/day
1	01-Jul-2022	2450	0
2	01-Jul-2022	65400	0
1	02-Jul-2022	2650	200
2	02-Jul-2022	66400	1000
1	03-Jul-2022	2950	300
1	04-Jul-2022	3800	850
2	06-Jul-2022	70100	925
1	07-Jul-2022	6050	750
2	07-Jul-2022	71000	900

[marcus_sommer · Accepted solution]

It might be done with interrecord-functions - something like:

load *, if(Truck = previous(Truck), Odo - peek('Odo'), 0) as [km/day]
resident Source order by Truck, Date desc;

- Marcus

[ITSPA]

Thanks Marcus... it didn't work for me.. gives me error

I am working on the DB before qv... maybe it is easier... what do you think people?

select h.*,
(select h2.date
from t h2
where h2.truck = h.truck and h2.date < h.date
order by h2.date desc
limit 1
) as prev_date,
(select h2.ODO
from t h2
where h2.truck = h.truck and h2.date < h.date
order by h2.`Fecha` desc
limit 1
) as prev_ODO
from t h;

[marcus_sommer]

Of course you may do this part already within the data-base - if it's easier for you and the performance is good enough.

Important is by such a Qlik approach that it's done completely within Qlik and not in parts within the sql-part, that it's applied within a resident-load with an appropriate ordering.

- Marcus

[ITSPA]

@marcus_sommer I tried your approach... and it is better. THANKS!!!