Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Load Field with MAX date

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

1 Solution

Accepted Solutions
darkhorse
Not applicable

Re: Load Field with MAX date

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

13 Replies
its_anandrjs
Not applicable

Re: Load Field with MAX date

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

Not applicable

Re: Load Field with MAX date

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

darkhorse
Not applicable

Re: Load Field with MAX date

group by Order;

BR

Serhan

Not applicable

Re: Load Field with MAX date

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

darkhorse
Not applicable

Re: Load Field with MAX date

exactly.

BR

Serhan

its_anandrjs
Not applicable

Re: Load Field with MAX date

Hi,

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

Order        Date

A                12/11/2014


Regards

Anand

Not applicable

Re: Load Field with MAX date

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.

Not applicable

Re: Load Field with MAX date

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.

darkhorse
Not applicable

Re: Load Field with MAX date

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