write another column based on recent date

[flames]

Hello,

I am trying to get most recent value of CODE for a given ZIP.

for example, this is my input table.

CODE	ZIP	DATE(C_DATE)
PC	98K36B	7/25/2018
DK79KSJ	98K36B	12/11/2018
XK	78J76D	8/25/2018
HU897GT	78J76D	6/11/2018

Now, for ZIP 98K36B, 12/11/2018 is recent date, corresponding CODE for 12/11/2018 is DK79KSJ.

so my result should be only based on recent Date. 

Here is my desired output. 

please help with query to get this. Thank you.

CODE	ZIP	DATE(C_DATE)
DK79KSJ	98K36B	12/11/2018
XK	78J76D	8/25/2018

[lblumenfeld]

I read this as meaning that you want to return the latest ("max") date for a zip code match. If so then try this.

After this code executes, you'll have a mapping table called ZipMap. You'd use it in an ApplyMap as follows:

ApplyMap('ZipMap', Zip) as Code;

That will return the correct code for each zip.

MyData:
Load * Inline [
Code, Zip, ZipDate
PC,	98K36B, 7/25/2018
DK79KSJ, 98K36B, 12/11/2018
XK, 78J76D, 8/25/2018
HU897GT, 78J76D, 6/11/2018
];

// You don't need the table above. I'm just loading your data.

TempZipTable:
NoConcatenate Load
  Code,
  Zip,
  ZipDate
Resident MyData; 
// Replace MyData above, with the SQL statement that loads your data.

Drop Table MyData;

ZipData:
NoConcatenate Load
  Zip,
  Max(ZipDate) as ZipDate
Resident TempZipTable
Group By Zip;

ZipData:
Left Join (ZipData)
Load
  Zip,
  ZipDate,
  Code
Resident TempZipTable;
// ZipDate now contains the Zip, ZipDate, Code for each last Date

Drop Table TempZipTable;

ZipMap:
Mapping Load
  Zip,
  Code
Resident ZipData;

Drop Table ZipData;

[tresesco]

Firstsortedvalue() could help you get this easily. Try like:

Load
        Firstsortedvalue(CODE, -C_DATE) as CODE,
        FirstsortedValue(ZIP, -C_DATE) as ZIP,
        Date(Max(C_DATE)) as C_DATE

[flames]

unfortunately, it's not writing anything to the table. 

[flames]

Thanks, But, either I am lost to replicate or it's not working 😞 😞

[tresesco · Accepted solution]

Check this:

Load
        Firstsortedvalue(CODE, -C_DATE) as CODE,
        FirstsortedValue(ZIP, -C_DATE) as ZIP,
        Date(Max(C_DATE)) as C_DATE
Group By ZIP;        
	
Load * Inline [
CODE,	ZIP,	C_DATE
PC,	98K36B,	7/25/2018
DK79KSJ,	98K36B,	12/11/2018
XK,	78J76D,	8/25/2018
HU897GT,	78J76D,	6/11/2018]

[ahmetozmen]

Hi, 

the solution: 

Data:
Load *Inline [
CODE,ZIP,DATE(C_DATE)
PC, 98K36B, 7/25/2018
DK79KSJ, 98K36B, 12/11/2018
XK, 78J76D, 8/25/2018
HU897GT, 78J76D, 6/11/2018
];

Inner Join
Load
ZIP,
max("DATE(C_DATE)") as "DATE(C_DATE)"
Resident Data
Group By ZIP ;

;

[flames]

Thanks, it worked. 

In the output you sent, the result is wrong, it should pick the recent date.

But, the query is right, I got my required answer. Thanks again.

[flames]

Thanks for the Answer 🙂