Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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:
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;
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!
Hi,
I have edited my code, that should solve your problem.
If a person has single date, it will mark as first.
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:
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;
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....