Latest Date

[Anonymous]

Hi

I have a table which shows roster periods and when they were published.  I want to include something in the script which identifies the latest published date.  My table is below :

PK_Date	Period	Publish_Date	Published
01/12/2015	01/12/15 - 14/12/15	01/11/2015	Y
08/12/2015	01/12/15 - 14/12/15	01/11/2015	Y
15/12/2015	15/12/15 - 28/12/15	15/11/2015	Y
22/12/2015	15/12/15 - 28/12/15	15/11/2015	Y
29/12/2015	29/12/15 - 12/01/16	29/11/2015	N
05/01/2016	29/12/15 - 12/01/16	29/11/2015	N

I want to add a line in the script which does the following :

PK_Date	Period	Publish_Date	Published	Latest_Published
01/12/2015	01/12/15 - 14/12/15	01/11/2015	Y
08/12/2015	01/12/15 - 14/12/15	01/11/2015	Y
15/12/2015	15/12/15 - 28/12/15	15/11/2015	Y
22/12/2015	15/12/15 - 28/12/15	15/11/2015	Y	Latest Published
29/12/2015	29/12/15 - 12/01/16	29/11/2015	N
05/01/2016	29/12/15 - 12/01/16	29/11/2015	N

Could anyone help ?

Thanks

Phil

[rubenmarin]

Hi Phil, after you have loaded your table you can add the text to the record using 'first' and 'order by', ie:

Data:

LOAD Yourdata...

FROM ...

Left Join(Data)

First 1 LOAD *, 'Latest Published' as Latest_Published resident Data where Published='Y' order by Publish_Date desc, PK_Date desc;