Max Date Help

[#1]

Hello

I am new to Qlikview, so apologies in advance if my question appears a little clumsy.

I have a very large table (over 5,000,000 records)  and would like to organise my data by flagging which Contract Account numbers have received a letter in a the past 6 months (Run Date). I would like to know the best way to use the max of the Run Date field to create a list box where I filter between over and under 6 months (since the last Run Date).

Any help would be hugely appreciated.

Thanks,

Glen

[#2 · Accepted solution]

Try like below:

DATA:

LOAD * FROM SOURCE.qvd (qvd);

MAX_RUNDATE:

LOAD ACCT_NO, Max(RUN_DATE) AS RUN_DATE

FROM SOURCE.qvd (qvd) Group By ACCT_NO;

[#3]

Thanks Dathu - that's very helpful.

This works well but creates two linked tables, is there anyway I can append this to the original data to store as a QVD?

Glen

[#4]

Simply Left join the table like below:

DATA:

LOAD * FROM SOURCE.qvd (qvd);

LEFT JOIN

LOAD ACCT_NO, Max(RUN_DATE) AS RUN_DATE

FROM SOURCE.qvd (qvd) Group By ACCT_NO;

STORE DATA into DATA.QVD (qvd) ;

DROP TABLE DATA ;

Please mark the question is answered if you get the solution.

[#5]

Thanks Dathu - this works perfectly.

Glen