Load Between Dates

[#1]

Hi

I have two tables that I would like to join. One table has a date and a fuel price. the fuel price changes at irregular intervals.

FuelPrice:

LOAD * INLINE [_Key, FuelDate, FuelPrice

Q-14, 23.03.2016, 22.88

Q-14, 29.03.2016, 22.18

Q-14, 31.03.2016, 21.24

];

Flights:

LOAD * INLINE [_Key, FlightNo, Origin, Destination, FlightDate

Q-14, RC 1000, WUH, PEK, 25.03.2016

Q-14, RC 1000, WUH, PEK, 26.03.2016

Q-14, RC 1000, WUH, PEK, 27.03.2016

Q-14, RC 1000, WUH, PEK, 28.03.2016

Q-14, RC 1000, WUH, PEK, 29.03.2016

Q-14, RC 1000, WUH, PEK, 30.03.2016

Q-14, RC 1000, WUH, PEK, 31.03.2016

Q-14, RC 1000, WUH, PEK, 01.04.2016

Q-14, RC 1000, WUH, PEK, 02.04.2016

Q-14, RC 1000, WUH, PEK, 03.04.2016

Q-14, RC 1000, WUH, PEK, 04.04.2016

];

I would like to link the FuelPrice to the flights table and have the corresponding FuelPrice for each date.

Flights:

_Key, FlightNo, Origin, Destination, FlightDate, FuelPrice

Q-14, RC 1000, WUH, PEK, 25.03.2016, 22.88

Q-14, RC 1000, WUH, PEK, 26.03.2016, 22.88

Q-14, RC 1000, WUH, PEK, 27.03.2016, 22.88

Q-14, RC 1000, WUH, PEK, 28.03.2016, 22.88

Q-14, RC 1000, WUH, PEK, 29.03.2016, 22.18

Q-14, RC 1000, WUH, PEK, 30.03.2016, 22.18

Q-14, RC 1000, WUH, PEK, 31.03.2016, 21.24

Q-14, RC 1000, WUH, PEK, 01.04.2016, 21.24

Q-14, RC 1000, WUH, PEK, 02.04.2016, 21.24

Q-14, RC 1000, WUH, PEK, 03.04.2016, 21.24

Q-14, RC 1000, WUH, PEK, 04.04.2016, 21.24

];

Using an Interval Match should probably work, though I only have one date field in the FuelPrice table. How could I easily add the previous FuelDate as an EndFuelDate or if it's the last record using the current date?

Or is there a simpler version to do that?

[kaushiknsolanki · Accepted solution]

Hi,

Try this script to get start and end date and then you can use the interval match to link that with other table.

FuelPrice:

LOAD *,Previous(FuelDate) as PreviousDate INLINE [_Key, FuelDate, FuelPrice

Q-14, 23.03.2016, 22.88

Q-14, 29.03.2016, 22.18

Q-14, 31.03.2016, 21.24

];

Left join

Load PreviousDate as FuelDate, FuelDate as EndDate

Resident FuelPrice;

Drop field PreviousDate;

Regards,

Kaushik Solanki

[avinashelite]

First re-build the first table with all the necessary details(please follow the method suggested by Kaushik.solanki and then  link the tables ,

I would suggest you to build a composite key like KEY&'-'&Date as New_Key