Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
jiwaniakbiar
Contributor II
Contributor II

Flag Latest among duplicate rows

This is the table I am working with (Correct Record Flag) is not included in the table. I want to add this during load script.

I want to implement logic to mark Correct Record Flag when there are more than one rows with same passcode

1. If there is only one row with the passcode, mark this flag as "Y" (example: zz2832s, tu28cqc)

2. If there are more than one rows then following are the scenarios

- If there are two rows -One with Status 'Partial' and other 'Complete' then mark row with 'Complete' as "Y" and "Partial" as "N" (example: 55fez86)

- If there are two rows both with Same status i.e. both Partial or both Complete, then mark one with lastest based on Date Submitted  (Example: 9928cos)

- if there are 3 or more rows with combination of status, then pick the latest "Complete" and markt it as "Y" (Example: 1zr5mjc)

Response IDTime StartedDate SubmittedStatusPasscodeCorrect Record Flag
120/02/2019 11:0420/02/2019 21:34Partial1zr5mjcN
220/02/2019 11:5120/02/2019 22:22Complete1zr5mjcY
320/02/2019 21:0021/02/2019 7:33Partial1zr5mjcN
421/02/2019 0:5621/02/2019 11:27Partial55fez86N
521/02/2019 0:5521/02/2019 11:34Complete55fez86N
621/02/2019 3:0421/02/2019 13:35Complete55fez86Y
720/02/2019 11:5320/02/2019 22:24Partial9928cosN
820/02/2019 11:5320/02/2019 23:24Partial9928cosY
920/02/2019 11:5321/02/2019 21:24Partialtu28cqcY
1020/02/2019 11:5322/02/2019 23:34Completezz2832sY

 

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

Try this

Table:
LOAD * INLINE [
    Response ID, Time Started, Date Submitted, Status, Passcode
    1, 20/02/2019 11:04, 20/02/2019 21:34, Partial, 1zr5mjc
    2, 20/02/2019 11:51, 20/02/2019 22:22, Complete, 1zr5mjc
    3, 20/02/2019 21:00, 21/02/2019 7:33, Partial, 1zr5mjc
    4, 21/02/2019 0:56, 21/02/2019 11:27, Partial, 55fez86
    5, 21/02/2019 0:55, 21/02/2019 11:34, Complete, 55fez86
    6, 21/02/2019 3:04, 21/02/2019 13:35, Complete, 55fez86
    7, 20/02/2019 11:53, 20/02/2019 22:24, Partial, 9928cos
    8, 20/02/2019 11:53, 20/02/2019 23:24, Partial, 9928cos
    9, 20/02/2019 11:53, 21/02/2019 21:24, Partial, tu28cqc
    10, 20/02/2019 11:53, 22/02/2019 23:34, Complete, zz2832s
];

FinalTable:
LOAD *,
	 If(Passcode = Previous(Passcode), 'N', 'Y') as [Correct Record Flag]
Resident Table
Order By Passcode, Status, [Date Submitted] desc;

DROP Table Table;

View solution in original post

3 Replies
sunny_talwar

Try this

Table:
LOAD * INLINE [
    Response ID, Time Started, Date Submitted, Status, Passcode
    1, 20/02/2019 11:04, 20/02/2019 21:34, Partial, 1zr5mjc
    2, 20/02/2019 11:51, 20/02/2019 22:22, Complete, 1zr5mjc
    3, 20/02/2019 21:00, 21/02/2019 7:33, Partial, 1zr5mjc
    4, 21/02/2019 0:56, 21/02/2019 11:27, Partial, 55fez86
    5, 21/02/2019 0:55, 21/02/2019 11:34, Complete, 55fez86
    6, 21/02/2019 3:04, 21/02/2019 13:35, Complete, 55fez86
    7, 20/02/2019 11:53, 20/02/2019 22:24, Partial, 9928cos
    8, 20/02/2019 11:53, 20/02/2019 23:24, Partial, 9928cos
    9, 20/02/2019 11:53, 21/02/2019 21:24, Partial, tu28cqc
    10, 20/02/2019 11:53, 22/02/2019 23:34, Complete, zz2832s
];

FinalTable:
LOAD *,
	 If(Passcode = Previous(Passcode), 'N', 'Y') as [Correct Record Flag]
Resident Table
Order By Passcode, Status, [Date Submitted] desc;

DROP Table Table;
jiwaniakbiar
Contributor II
Contributor II
Author

Perfect. This works. Thanks Tarun for your help

sunny_talwar


@jiwaniakbiar wrote:

Thanks Tarun for your help


Tarun?