4 Replies Latest reply: Apr 4, 2018 11:16 PM by Romila Bhagat RSS

    To fetch a single row from multiple row based on the latest date

    Romila Bhagat

      Hi,

       

      I want to export a single row for each IssueKey based on the latest entry of TaskDate column that i have in my code.

      I am combining 2 spreadsheet using left join and the output that i get on that i want to apply filter to get a single record for the multiple entries that i am getting for a given IssueKey.

      Please find below the codes for reference.

      Also please find attached the screenshot of the output that i am getting.

       

      FI:

      LOAD

          "Sl no.",

          "Old Meter Id" as Old_Meter,

          "Old Lce Id" as Old_LCE,

          "Property Id" as PropertyID,

          "Meter Type",

          "TWSMOC Id" as IssueKey,

          Label,

          "BPMS/JMS",

          "Job Ref#" as JobRef,

          "Job Type",

          "Date Sent"

      FROM [lib://Field Investigation/Master sheet1.csv]

      (txt, codepage is 28591, embedded labels, delimiter is ',', msq);


      Left Join(FI)

      LOAD

      Filename() as TWMFilename,

      DATE(DATE#(LEFT(RIGHT(Filename(),10),6),'DDMMYY'),'DD/MM/YYYY') as TWMFiledate,

          "Job Reference" as JobRef,

          "Installation Completed Comments" as Operative_Comments,

          "Job Code",

          "Job Status",

          "Job Sent to TW",

          TaskDate,

          //DATE(TaskDate, 'DD/MM/YYYY') as TaskDates,

          "Meter Serial No",

          "Meter Serial Number" as Old_Meter,

          "Old LCE Serial Number",

          "Old LCE Reader Serial Number",

          "CIS Property Reference: CIS Property Reference" as PropertyID,

          "Serial No: Serial Number" as New_Meter,

          "LCE Reader Serial No" as New_LCE

      FROM [lib://Field Investigation/TWMReport_030418.csv]

      (txt, codepage is 28591, embedded labels, delimiter is ',', msq);


      Inner Join(FI)

      Load

      Distinct IssueKey,

      //FirstSortedValue(DISTINCT TaskDates,-IssueKey)

      max(TaskDates) as MaxTaskDate

      Resident FI

      Group by IssueKey;

       

      Regards,

      Romila.