Load Scrip pick value with MAX date

[#1]

Hi,

what I need is something like this in one load statement:

Example;

TableA:

Field Header:         Order     |         Date     |         Customer       |    

Field Content:          A                12/11/2014               John                                  

                                A                12/10/2014                John                                   

                                B                12/07/2014                Paul                                   

                                B                12/05/2014                Paul                                   

What I need is a load statement which would give me as an output the Order with the latest Date.

LOAD
  Order,
  Customer,
  max(DATE)
FROM
TableA   (qvd)

Expected results:

Order           Date

A               12/11/2014

B               12/07/2014

Actual results:

Order        Date

A                12/11/2014

[morganaaron]

Hi Ivan,

If you load your first table without the function, so just as the Date field, and then add a resident load that groups by Order, so:

Load

Order,

Date(Max(Date)) as MaxDate

Resident TableA

Group by Order;

It should give you what you want.

[#3]

now I have specified what I need in a more clear sense I guess.

okay so far so good (and here the tricky part) so far  I have been.

But now imagine the customer name has changed as well

Field Header:         Order     |         Date     |         Customer       |    

Field Content:          A                12/11/2014               John                                 

                                A                12/10/2014                John                                  

                                B                12/07/2014                Marc                                   

                                B                12/05/2014                Paul                                   

The output should have just:

Order           Date               Customer

A               12/11/2014               John

B               12/07/2014              Marc

then just --> group by Order, Customer;  would not work correct? As it would consider Paul as an separate line.

[its_anandrjs]

Hi,

By the Date field you can do the same with some table loads as the result

Raw:
LOAD Order, Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') as Date, Customer;
LOAD * INLINE [
    Order, Date, Customer
    A, 12/11/2014, John
    A, 12/10/2014, John
    B, 12/07/2014, Paul
    B, 12/05/2014, Paul  ];  

Expected:
LOAD
Order, Customer as [Expected Customer],
Date(Max(Date),'MM/DD/YYYY') as MaxDate
Resident Raw Group By Order,Customer Order By Date desc;

LET vMaxDate = Peek('MaxDate',0,'Expected');

ActuatResult:
NoConcatenate
LOAD
Order as [Actual Order],[Expected Customer] as [Customer Name],MaxDate as [Expected Date]
Resident Expected Where MaxDate = '$(vMaxDate)' and Len([Expected Customer]) > 0;

Regards

Anand