topic Re: Max Date Help in QlikView

Max Date Help

Tue, 17 Dec 2013 16:38:54 GMT

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

Re: Max Date Help

Wed, 18 Dec 2013 14:25:57 GMT

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;

Re: Max Date Help

Thu, 19 Dec 2013 16:28:43 GMT

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

Re: Max Date Help

Thu, 19 Dec 2013 16:45:38 GMT

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.

Re: Max Date Help

Fri, 20 Dec 2013 13:05:18 GMT

Thanks Dathu - this works perfectly.

Glen