Max Date Needed.....

[tmumaw]

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

[tmumaw]

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

Thom

[#3]

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;

[tmumaw]

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

[#5]

yes,only entry

see attached

[tmumaw]

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

Thanks

[tmumaw]

Sorry attached the wrong file…..

[#8]

see attached

[#9]

hav u gt d ansr?

[tmumaw]

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.

[tmumaw]

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