Getting the max date infro from the table

[#1]

Hi guys!

Need your help!

I have an Excel table like this:

I need to find the max date of each person and get the Account name.

Please help!

[Anonymous]

Let's try...

Data:

LOAD

     Employee,

     date(max(StartDate)) as MaxDate

FROM <YourExcelFile>

GROUP BY Employee;

LEFT JOIN (Data) LOAD

LOAD

     Employee,

     Account

FROM <YourExcelFile>;

[#3]

Hi, Michael.

I think I havent written all the requirements above. The thing is that I also need to take into consideration other columns like : January, February etc. Need to find the latest month of the latest Account where the value is not null. Also there can be 2 accounts at the same period I need to find the account with the biggest value.

[Anonymous]

I don't understand

Try to explain with an example.

[#5]

I've made a crosstable out of this. Here I have a new table:

Employee	Account	Month	Data
Pavel Kovalski	International Other	01.01.14
Pavel Kovalski	International Other	01.01.15	0
Pavel Kovalski	International Other	01.01.15	1,00
Pavel Kovalski	International Other	01.02.14	0
Pavel Kovalski	International Other	01.02.15	0
Pavel Kovalski	International Other	01.02.15	1,00
Kirill Abramov	Banks	01.03.14	0
Kirill Abramov	Banks	01.04.14	1
Kirill Abramov	Banks	01.05.14
Kirill Abramov	Banks	01.06.14
Kirill Abramov	Banks	01.07.14

I need to find the latest month with the biggest value of each person, thus I will find the the latest active account of a person.

[Anonymous · Accepted solution]

Still not very clear, so I have to make assumptions that could be right or wrong:

1. Month field format is DD.MM.YYYY, that is 01.02.14 is February 2014.
2. You want to find the largest value per person, and then the last date when it value occurs.  And, you want this in the script, not the front end.
3. Find the Account of the record identified in #2.  (I'm confused about this because in your example Account to Person is one-to-one relation, there is nothing much to find.)

Anyway, if my assumptions are correct, see the attached app.  Either it helps you to go forward, or you'll clarify further what you need.