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.
