topic Max Date Needed..... in QlikView

Max Date Needed.....

tmumaw — Fri, 26 Oct 2012 12:40:14 GMT

Good morning all,

Need your help getting the maxdate from a employee table. What I have is multiple entries for the same employee with different WEDAT's. I only need my file to contain the most current WEDAT record. How can I achieve this? I would like to do it in my load script.

Thanks

[As of Date]    AS [WEDAT],
[Cost Ctr]        AS [Profit Center],
[Emp #]            AS [All.Emp #],
[Activity]          AS [JobCode],
[Emp Subgrp] AS [All.Emp Subgrp],
[Date 2]             AS [All.Date 2],
[As of Date] & [Cost Ctr] & [Emp #] & [Activity] & [Emp Subgrp] & [Date 2] as ConKey

Re: Max Date Needed.....

MayilVahanan — Fri, 26 Oct 2012 12:56:27 GMT

Try like this

Test:

Load

[As of Date]    AS WEDAT,
[Cost Ctr]        AS [Profit Center],
[Emp #]            AS [All.Emp #],
[Activity]          AS [JobCode],
[Emp Subgrp] AS [All.Emp Subgrp],
[Date 2]             AS [All.Date 2],
[As of Date] & [Cost Ctr] & [Emp #] & [Activity] & [Emp Subgrp] & [Date 2] as ConKey

from tablename;

inner join(Test)

Load max(WEDAT) as WEDAT resident Test;

Hope it helps;

Re: Max Date Needed.....

Fri, 26 Oct 2012 13:37:23 GMT

tab1:

LOAD

max(date([As of Date]) AS max_WEDAT,

[Emp #] [from ur source]

group by [Emp #] ;

tab2:

LOAD date(max_WEDAT) &'|'& [Emp #] as [WEDAT_Emp] Resident tab1;

tab3:

date(max_WEDAT) &'|'& [Emp #] as [WEDAT_Emp],

[from ur source] where exists([WEDAT_Emp], date(max_WEDAT) &'|'& [Emp #]);

drop table tab2;

You can either drop tab1 or keep it to see the max WEDATE for each #Emp.

Hope its useful !!!

Regards

Re: Max Date Needed.....

tmumaw — Fri, 26 Oct 2012 13:56:56 GMT

TAB1:

LOAD

max(date()) AS max_WEDAT,

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

group by ;

TAB2:

LOAD date(max_WEDAT) &'|'& as

Resident TAB1;

TAB3:

LOAD

date(max_WEDAT) &'|'& as ,

AS ,

& & & & & as ConKey

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where exists(, date(max_WEDAT) &'|'& );

drop table TAB2;

I don’t have the field labeled max_WEDAT on my source file. Should it just be WEDAT?

Thanks

Thom

Re: Max Date Needed.....

Fri, 26 Oct 2012 14:02:25 GMT

u dont need max_WEDATE field form ur source

have u tried the code as i posted ?

just put whatever the date field nameis there in ur source of which max date per emp is needed.

you've mentioned [As of Date] field in ur case.

Regards

Re: Max Date Needed.....

tmumaw — Fri, 26 Oct 2012 14:10:44 GMT

Yes. I have tried what you posted. The only error I am getting is what I have highlighted.

Thanks

Re: Max Date Needed.....

Fri, 26 Oct 2012 14:16:15 GMT

i'm sure the solution will work.

Can u pls attach ur qvw ?

Re: Max Date Needed.....

tmumaw — Fri, 26 Oct 2012 14:20:56 GMT

Thanks

Re: Max Date Needed.....

Fri, 26 Oct 2012 14:32:08 GMT

can u pls load only this part of script & post if u get any error

i can't do it as i dnt have the csv file.

Re: Max Date Needed.....

Fri, 26 Oct 2012 14:41:08 GMT

u shud only have 2 tables in ur data model then....Tab1 & Tab3 linked by [All.Emp #].

Re: Max Date Needed.....

tmumaw — Fri, 26 Oct 2012 14:49:54 GMT

Here is a CSV file with a sample of 1 employee with multiple dates. Thanks again for all your help.

Thom

Re: Max Date Needed.....

Fri, 26 Oct 2012 15:16:48 GMT

2 points:

1) in Tab 3 i directly used max_WEDATE, it shud have been [As of Date] hence the script failed

2) now the major point, there're two different date formats in ur csv so it had to be made consistent using alt function.

use below script

TAB1:

LOAD

max(date(alt(date#([As of Date] , 'M/DD/YYYY' ),date#([As of Date] ,'MM-DD-YY'),0))) AS max_WEDAT,

[Emp #] AS [All.Emp #]

[from ur source] group by [Emp #];

TAB2:

LOAD date(max_WEDAT) &'|'& [All.Emp #] as [WEDAT_Emp]

Resident TAB1;

TAB3:

LOAD

date(alt(date#([As of Date] , 'M/DD/YYYY' ),date#([As of Date] ,'MM-DD-YY'),0)) AS [WEDAT],

date(alt(date#([As of Date] , 'M/DD/YYYY' ),date#([As of Date] ,'MM-DD-YY'),0)) &'|'& [Emp #] as [WEDAT_Emp],

// [As of Date] AS [WEDAT],

[Cost Ctr] AS [Profit Center],

[Emp #] AS [All.Emp #],

[Activity] AS [JobCode],

[Emp Subgrp] AS [All.Emp Subgrp],

[Date 2] AS [All.Date 2],

[As of Date] & [Cost Ctr] & [Emp #] & [Activity] & [Emp Subgrp] & [Date 2] as ConKey

[from ur source]

where exists([WEDAT_Emp], date(alt(date#([As of Date] , 'M/DD/YYYY' ),date#([As of Date] ,'MM-DD-YY'),0)) &'|'& [Emp #]);

drop table TAB2;

Re: Max Date Needed.....

tmumaw — Fri, 26 Oct 2012 15:37:19 GMT

Did you only get one entry in your qvw for EmpID 11? When I run it I get 3. One for ( 9/20/2008, 10/8/2011, 3/24/2012) I only want the one for 2012 because it’s the most current. We are very close and I really do appreciate all your help.

Thom

Re: Max Date Needed.....

Fri, 26 Oct 2012 15:44:08 GMT

yes,only entry

see attached

Re: Max Date Needed.....

tmumaw — Fri, 26 Oct 2012 15:51:07 GMT

Here is a new CSV file. Could you please try it?

Thanks

Re: Max Date Needed.....

tmumaw — Fri, 26 Oct 2012 15:55:55 GMT

Sorry attached the wrong file…..

Re: Max Date Needed.....

Fri, 26 Oct 2012 16:07:39 GMT

see attached

Re: Max Date Needed.....

Fri, 26 Oct 2012 17:35:28 GMT

hav u gt d ansr?

Re: Max Date Needed.....

tmumaw — Fri, 26 Oct 2012 17:57:44 GMT

What happens when you concatenate multiple CSV files? Should it act as one file or multiple files? When I take one file in it works fine, but when I concatenate them I get multiples.

Re: Max Date Needed.....

tmumaw — Fri, 26 Oct 2012 18:10:20 GMT

Here are 2 csv files with only employee ID 11 on.