Load script question

[#1]

Hello,

I have two tables ,

table one with multiple orders from different administrations

Orders:

RideLink,OrderID,OrderCreateDate,

Rides:

Table two with multiple rides from different administrations

Ridelink, RideNumber, RideCreatedate, RideAdministration

In a Load script I want to have the last rideAdministration based on latest OrderCreateDate

What is the best way to do this?

[maxgro]

could you post some data?

[Anonymous]

Hi,

I am not sure if this is what you are looking for.

If you identify what is the latest order create date by RideLink, you will be one step closer, I think.

T0:

LOAD

RideLink

Max(OrderCreateDate) as MaxOrderCreateDate

Resident Orders

GROUP BY RideLink;

I lack the business logic to link tha last RideAdministration based on Latest OrderCreateDate.

[jonathandienst]

Hi

If you simply load the two tables, they will associate using the key field RideLink. Then you can use a function like this in the front end:

          = FirstSortedValue(RideAdministration, -OrderCreateDate)

to get the RideAdministration linked to the most recent OrderCreateDate.

HTH

Jonathan

[#5]

I did try this before, but because of the number of rows it takes to long.

[#6]

Eruditio,

The order is going to several decentralize administrations but keeps the same order number, in every administrations the ride changes. and ill want to know what he last ride was.

[Anonymous]

- Does the Rides table contain the order number?

- Does the RideLink provide a 1-1 relationship with the Order Number in the Orders table ?

[#8]

1. The Rides table is not containing the Ordernumber, but is linked through Ridelink on the order table which contains Ordernumber.

2. RideLink is a combination of the administration code and the RideNumber and is a 1-1 relation between Rides and Orders.

The ordernumber in the order table can occur multiple times in order table because an order can go from one Administration to another.

[Anonymous · Accepted solution]

If you identify what is the latest order create date by RideLink

T0:

LOAD

RideLink,

Max(OrderCreateDate) as MaxOrderCreateDate

Resident Orders

GROUP BY RideLink;

NoConcatenate

Map1:

MAPPING LOAD

RideLink,

MaxOrderCreateDate

Resident T0;

DROP TABLE T0;

and you identify what is the last ride by RIdeLink:

T1:

LOAD

RideLink,

Max(RideCreatedate) as MaxRideCreateDate

Resident Rides

GROUP BY RideLink;

Then link both to have a singe table that contains, per ridelink, the latest ride and the latest order.

T2;

LOAD *,

ApplyMap('Map1', RIdeLink) as MaxOrderCreateDate

Resident T1;

DROP TABLE T1;

Loading this table into your data model will allow you to associate per administrator, the latest ride and order id.

Hope it helps,

Antoine

[#10]

yes i can make something of this

thanks