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

[MayilVahanan]

HI

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;

[#3]

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],

[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(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

[tmumaw]

TAB1:

LOAD

max(date()) AS max_WEDAT,

AS

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 ,

AS ,

AS ,

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

[#5]

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

[tmumaw]

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

Thanks

[#7]

i'm sure the solution will work.

Can u pls attach ur qvw ?

[tmumaw]

Thanks

[#9]

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.

[#10 · Accepted solution]

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