Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
Creator
Creator

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

View solution in original post

6 Replies
Highlighted
Partner
Partner

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;

Highlighted
Creator
Creator

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!

Highlighted
Partner
Partner

Hi,

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

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

Highlighted
Creator
Creator

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

View solution in original post

Highlighted
Creator
Creator

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;

Highlighted
Creator
Creator

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