topic Re: Load Scrip pick value with MAX date in QlikView

Load Scrip pick value with MAX date

Thu, 11 Dec 2014 11:39:59 GMT

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

Re: Load Scrip pick value with MAX date

morganaaron — Thu, 11 Dec 2014 11:56:36 GMT

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.

Re: Load Scrip pick value with MAX date

Thu, 11 Dec 2014 11:59:06 GMT

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.

Re: Load Scrip pick value with MAX date

its_anandrjs — Thu, 11 Dec 2014 12:34:22 GMT

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