topic Re: Load Field with MAX date in QlikView

Load Field with MAX date

Thu, 11 Dec 2014 10:06:22 GMT

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

Re: Load Field with MAX date

its_anandrjs — Thu, 11 Dec 2014 10:12:23 GMT

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

Re: Load Field with MAX date

Thu, 11 Dec 2014 11:34:04 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 Field with MAX date

Anonymous — Thu, 11 Dec 2014 11:42:37 GMT

group by Order;

Serhan

Re: Load Field with MAX date

Thu, 11 Dec 2014 11:49:27 GMT

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

Re: Load Field with MAX date

Anonymous — Thu, 11 Dec 2014 11:50:20 GMT

exactly.

Serhan

Re: Load Field with MAX date

its_anandrjs — Thu, 11 Dec 2014 11:51:26 GMT

Hi,

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

Order Date

A 12/11/2014

Regards

Anand

Re: Load Field with MAX date

Thu, 11 Dec 2014 11:53:41 GMT

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 Field with MAX date

Thu, 11 Dec 2014 11:58:36 GMT

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 Field with MAX date

Anonymous — Thu, 11 Dec 2014 12:29:48 GMT

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.

Serhan

Re: Load Field with MAX date

Thu, 11 Dec 2014 12:30:57 GMT

I understand but my source isn't so clever unfortunately

Re: Load Field with MAX date

Anonymous — Thu, 11 Dec 2014 12:35:20 GMT

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.

Serhan

Re: Load Field with MAX date

Anonymous — Thu, 11 Dec 2014 12:38:04 GMT

yes that's right

Re: Load Field with MAX date

its_anandrjs — Thu, 11 Dec 2014 12:43:02 GMT

Hi,

Data should be consistent in this case they not change otherwise result changes if so then the proper field names is used with Key fields.

Regards
Anand