Load Calc field based on multiple criteria

[#1]

Hi All

I have been kicking this question around for a while and I just cant seem to get it right - I hope someone can help.

I have multiple data files that is a log of applications - as part of the load I need to identify if an application is open and if so what Team it is with.

I need to create a calculated field during load based on this criteria :

For each application = the latest date where it is Status = RDY AND there is no CMP for a later date in Team Closed

Any advise relly appreciated

Thanks

A

Application	Team	Date	Status	Calculated field
1	Input	01/01/2014 09:00	CMP
2	Input	01/01/2014 10:00	RDY
1	Verify	01/01/2014 11:00	RDY
1	Doc	02/01/2014 10:00	RDY
1	Print	02/01/2014 12:00	CMP
2	Verify	02/01/2014 13:00	CMP
2	Doc	01/01/2014 11:30	RDY	OPEN
3	Verify	01/01/2014 12:30	RDY	OPEN
3	Print	02/01/2014 12:00	CMP
1	Closed	02/01/2014 12:00	CMP

[marcus_sommer]

I think this could be solved with inter-record-functions in (nested) if-loops in a sorted load. The condition isn't really clear to me, therefore as abstract example:

sourcedata:

Load * From source;

sourcedataSort:

Load

     *,

     if(rowno() = 1, Status, if(Application = peek('Application', -1) and Status = 'XYZ', 'abc',

          if(......................))) as [Calculated field]

Resident sourcedata Order by Application, Date;

- Marcus

[#3]

Hi Marcus

Thanks for your response - can you please explain how the calculate Field is working - my understanding is the following :

If its the first row then take the status ( this will give a result either RDY or CMP )

or if the app number is not the same as the app number previous and the status us XYZ - 'ABC' .

I am wondering which is the part with the latest date ?

Can you help me apply the logic ?

Thanks

A

[marcus_sommer]

If(Application <> peek('Application', -1) then there is a new application with its startdate. Perhaps there is a better/easier way to check the latest date - per mapping and applymap:

MapLatestDate:

Mapping Load Application, max(Date) as MaxDate From Source Group By Application;

sourcedataSort:

Load

     *,

     if(Date = applymap('MapLatestDate', Date) and Status = 'XYZ' and peek('Status', -1) = 'ABC', 'abc',

          if(......................))) as [Calculated field]

Resident sourcedata Order by Application, Date;

- Marcus

[Gysbert_Wassenaar]

See attached qvw.

[maxgro]

see attachment

[#7]

Hi Massimo and Gysbert,

Both look great I will have a look this morning and see if I can apply to my data - thanks so much for taking the time to draft samples - I will respond with my findings ( questions!)

Thanks again

A

[#8]

Hi Massimo

Can you explain the alt part of the following ?

Thanks

A

and

MaxDateRDY

>=alt

(MaxDateCMP

,0

)