topic Re: Load script question in QlikView

Load script question

Tue, 20 May 2014 11:21:34 GMT

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?

Re: Load script question

maxgro — Tue, 20 May 2014 11:27:03 GMT

could you post some data?

Re: Load script question

Anonymous — Tue, 20 May 2014 11:49:12 GMT

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.

Re: Load script question

jonathandienst — Tue, 20 May 2014 11:53:03 GMT

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

Re: Load script question

Tue, 20 May 2014 12:09:39 GMT

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

Re: Load script question

Tue, 20 May 2014 12:16:01 GMT

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.

Re: Load script question

Anonymous — Tue, 20 May 2014 12:38:05 GMT

- Does the Rides table contain the order number?

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

Re: Load script question

Tue, 20 May 2014 13:02:16 GMT

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.

Re: Load script question

Anonymous — Tue, 20 May 2014 13:12:37 GMT

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

Re: Load script question

Tue, 20 May 2014 13:34:38 GMT

yes i can make something of this

thanks