topic Re: Getting the max date infro from the table in QlikView

Getting the max date infro from the table

Thu, 19 Mar 2015 08:51:08 GMT

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!

Re: Getting the max date infro from the table

Anonymous — Thu, 19 Mar 2015 14:20:27 GMT

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

Re: Getting the max date infro from the table

Thu, 19 Mar 2015 14:26:13 GMT

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.

Re: Getting the max date infro from the table

Anonymous — Thu, 19 Mar 2015 17:09:39 GMT

I don't understand

Try to explain with an example.

Re: Getting the max date infro from the table

Fri, 20 Mar 2015 08:07:13 GMT

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.

Re: Getting the max date infro from the table

Anonymous — Fri, 20 Mar 2015 13:16:26 GMT

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.