Solved: Re: Load Field with MAX date - Qlik Community

Report Inappropriate Content · ‎2014-12-11

Hi need help,

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.

Expected results:

Order Date

A 12/11/2014

B 12/07/2014

Anonymous · ‎2014-12-11

In that case you may start encouraging them to do that

What if they want you to tell them how many customers they have? Count which field?

It's impossible to be sure.

BR

Serhan

View solution in original post

its_anandrjs · ‎2014-12-11

Hi,

Try load load the

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 ];

Result:
LOAD
Order,
Date(Max(Date),'MM/DD/YYYY') as MaxDate
Resident Raw Group By Order;

By use of the Max function and aggregate the records in the load script

Order Date

A 12/11/2014

B 12/07/2014

Regards

Anand

Report Inappropriate Content · ‎2014-12-11

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

Anonymous · ‎2014-12-11

group by Order;

BR

Serhan

Report Inappropriate Content · ‎2014-12-11

and if I want as a result

Expected results:

Order Date Customer

A 12/11/2014 John

B 12/07/2014 Paul

then just --> group by Order, Customer; ?

Anonymous · ‎2014-12-11

exactly.

BR

Serhan

its_anandrjs · ‎2014-12-11

Hi,

Let me know in the result you want only single row that is the requirement

Order Date

A 12/11/2014

Regards

Anand

Report Inappropriate Content · ‎2014-12-11

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.

Report Inappropriate Content · ‎2014-12-11

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.

Anonymous · ‎2014-12-11

Hello,

I guess you are asking what if the name of customer changes. In that case you need a unique ID like CustomerID. That's a general rule independent of QlikView.

BR

Serhan