Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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]
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;
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
unfortunately, it's not writing anything to the table.
Thanks, But, either I am lost to replicate or it's not working 😞 😞
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]
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 ;
;
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.
Thanks for the Answer 🙂