Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
jadewind
Not applicable

label records with earliest and latest dates

Hi everyone,

My data contains people's attendance records. I need to label the records of the first attendance as "First" and latest attendance as "Last". In other words, I need to find the earliest date for the same person and mark it as "First" and latest date for the same person and mark it as "Last". So the table would look like below.

Person IDDateAttendance
1111/1/17First
2222/1/17
3331/1/17
1113/1/17
1114/1/17Last

How can I do it in Qlik Sense?

Thanks in advance!

1 Solution

Accepted Solutions
jadewind
Not applicable

Re: label records with earliest and latest dates

Thanks Shubham for your efforts. Much appreciated. I have modified your codes as below and it works exactly as I wish now:

MainData:

LOAD

    PersonID,

    "Date"

FROM [lib://sample/sample data for testing.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD

  PersonID,

  min(Date) as Date,

  If(Count(PersonID) >1, 'First') as Attendance

Resident MainData

Group By PersonID

;

concatenate(Temp)

LOAD

  PersonID,

  max(Date) as Date,

  If(Count(PersonID) >1, 'Last') as Attendance

Resident MainData

Group By PersonID

;

left join(MainData)

LOAD *,1 as Flag Resident Temp;

Drop Table Temp;

Drop Field Flag;

I don't want the single attendance to be marked as either First or Last. And now the above scripts get the result:

1.JPG

6 Replies
shubham_singh
Not applicable

Re: label records with earliest and latest dates

MainData:

LOAD PersonID,Date FROM Table.qvd;

Temp:

LOAD

PersonID,

min(Date) as DateNew,

'First' as Attendance

Resident MainData

Group By PersonID

;

concatenate(Temp)

LOAD

PersonID,

max(Date) as DateNew,

'Last' as Attendance

Resident MainData

where not exists(DateNew,Date)

Group By PersonID

;

left join(MainData)

LOAD

PersonID,

DateNew as Date

,1 as Flag Resident Temp;

Drop Table Temp;

Drop Field Flag;

jadewind
Not applicable

Re: label records with earliest and latest dates

Thanks for your reply Shubham.

I have used your method and the result table is as below:

Capture.JPG

I need the PersonIDs that do not have multiple dates to show null as Attendance as I don't want to count them in my calculation of First or Last.

How can I do it?

Thanks in advance!

shubham_singh
Not applicable

Re: label records with earliest and latest dates

Hi,

I have edited my code, that should solve your problem.

If a person has single date, it will mark as first.

jadewind
Not applicable

Re: label records with earliest and latest dates

Thanks Shubham for your efforts. Much appreciated. I have modified your codes as below and it works exactly as I wish now:

MainData:

LOAD

    PersonID,

    "Date"

FROM [lib://sample/sample data for testing.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD

  PersonID,

  min(Date) as Date,

  If(Count(PersonID) >1, 'First') as Attendance

Resident MainData

Group By PersonID

;

concatenate(Temp)

LOAD

  PersonID,

  max(Date) as Date,

  If(Count(PersonID) >1, 'Last') as Attendance

Resident MainData

Group By PersonID

;

left join(MainData)

LOAD *,1 as Flag Resident Temp;

Drop Table Temp;

Drop Field Flag;

I don't want the single attendance to be marked as either First or Last. And now the above scripts get the result:

1.JPG

jadewind
Not applicable

Re: label records with earliest and latest dates

Hi everyone again,

I have a new challenge now.

I need to count how many times each Person has attended and mark it as Freq for each record (so that whichever record I draw I will know how many times this person has attended). However I find after I have marked the Attendance as First or Last, the attendance in the middle is not marked with anything as Freq (as seen in attached image, the Freq for the middle one is marked as "-"). How can I mark the middle ones with the correct Freq number?

Please see attached for source data, and below for loading scripts:

YHC:

LOAD

    "Person ID",

    "Date"

FROM [lib://data extracts/testing data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD

  "Person ID",

  Count("Person ID") as Freq,

  min("Date") as "Date",

  If(Count("Person ID") >1, 'First') as Attendance

Resident YHC

Group By "Person ID"

;

concatenate(Temp)

LOAD

  "Person ID",

  Count("Person ID") as Freq,

  max("Date") as "Date",

  If(Count("Person ID") >1, 'Last') as Attendance

Resident YHC

Group By "Person ID"

;

left join(YHC)

LOAD *,1 as Flag Resident Temp;

Drop Table Temp;

Drop Field Flag;

jadewind
Not applicable

Re: label records with earliest and latest dates

I have found a workaround by doing the Freq as a separate table. But still wonder why the above scripts would get rid of the Freq for the middle attendances....