Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

robhagat
New Contributor

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

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.

4 Replies
MVP
MVP

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

Your screenshot is just a blank white page... could you please upload a new one?

robhagat
New Contributor

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

Hi ,

I have re-attached the screenshot in my post. Please see below the screenshot for your reference.

Thanks in advance.

FI screenshot.png

dwforest
Valued Contributor

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

What you did will append the MaxTaskDate to every record in your original file; now if you want to filter it.

F2:

NoConcatenate

LOAD * RESIDENT F1 WHERE TaskDate = MaxTaskDate;

robhagat
New Contributor

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

Thank you sooo much. It worked

Community Browser