label records with earliest and latest dates

[Anonymous]

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 ID	Date	Attendance
111	1/1/17	First
222	2/1/17
333	1/1/17
111	3/1/17
111	4/1/17	Last

How can I do it in Qlik Sense?

Thanks in advance!

[shubham_singh]

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;

[Anonymous]

Thanks for your reply Shubham.

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

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]

Hi,

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

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

[Anonymous · Accepted solution]

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:

[Anonymous]

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;

[Anonymous]

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....