Max Sequence to have Previous Sequence String Value

[#1]

Hi All,

I have this request to find out the latest User Name before the "CRR" Activity Code and then have a new field containing that name for the Maximum sequence of "CRR" code available for a ID.

Check this below table, I need "New Field Required" column.

For Instance: ID = In 246014, the latest CRR sequence =  3, but I need the name of previous (non CRR code) user name in the new field.

NOTE: I need everything to be done at script level.

ID	Seq Number	Activity Code	Completed By	New Field Required
242653	6	CR
242653	5	CRR	Martinez, Jacqueline	Myszka, Brooke
242653	4	CRR
242653	3	CRR
242653	2	CRR
242653	1	RA	Myszka, Brooke
246014	4	CR
246014	3	CRR	Osborne, Jodi	Thorne, Shawn
246014	2	BA	Thorne, Shawn
246014	1	BA

Thanks,

Angad

[marcus_sommer]

You could do this within a sorted load per Peek() or Previous() ?

- Marcus

[mikael_bjaveclo]

DATA:
Load *,
ID & '|' & [Seq Number] As myKey; // Unique for the record
Load * Inline [
ID, Seq Number, Activity Code, Completed By
242653, 6, CR,,
242653, 5, CRR, 'Martinez, Jacqueline'
242653, 4, CRR,,
242653, 3, CRR,,
242653, 2, CRR,,
242653, 1, RA, 'Myszka, Brooke',
246014, 4, CR,,
246014, 3, CRR, 'Osborne, Jodi'
246014, 2, BA, 'Thorne, Shawn',
246014, 1, BA ,,
];

// The CRR-records with a creator
Temp:
Load ID, myKey
Resident DATA
Where [Activity Code] = 'CRR' And Len([Completed By]);

// The other records with a creator
Left Join (Temp)
Load ID, [Completed By] As [First Completed By]
Resident DATA
Where [Activity Code] <> 'CRR' And Len([Completed By]);

// Join the hits
Left Join (DATA)
Load myKey, [First Completed By]
Resident Temp
Where Len([First Completed By]);
Drop Table Temp;
Drop Field myKey;