Show the latest status of RF Gun based on latest Date value

[codyt97]

Hey everybody. I've seen a lot of  solution but no luck applying on my problem. I don't have no problem if the solution is on frontend(expression QlikSense) or backend( Script)

I need to know if the RF gun is check-out or check-in based on last activity . For example below its showing two times RF Gun FU001  because there were 2 activities but what i want is to show only one time RF Gun FU001  with status 'Check-In' because the latest data in date columns  is 10:53AM.

Here is the script:

LOAD
ID,
"Start time",
"Completion time",
Email,
Name,
"Please Scan or type your full name",
"Please Scan or type your RF Gun ID",
"Check-In Or Check-Out"
FROM [lib://RF Gun Check-In SharePoint]
(ooxml, embedded labels, table is Form1);

Thank you in advance!

[vinieme12 · Accepted solution]

use a calculated dimension

Date

=if(Date= Aggr(nodistinct Max(Date),RF Gun),Date,null())   <----- uncheck show null values on this dimension

[vchuprina]

Hi,

In my opinion, it’s better to make transformations in the script than use calculated dimensions, for this reason, I recommend two solutions.

Calculate max date per ID and join(Inner join) both tables. You will get a necessary results, but part of the data will be removed from your app

Data:
LOAD
     ID,
     "Start time",
     "Completion time",
     Email,
     Name,
     "Please Scan or type your full name",
     "Please Scan or type your RF Gun ID",
     "Check-In Or Check-Out"
FROM [lib://RF Gun Check-In SharePoint]
(ooxml, embedded labels, table is Form1);

Inner Join(Data)
LOAD
     ID,
     Timestamp(MAX(Date)) AS Date
Resident Data
Group By ID;

Calculate max date per ID, add flag (IsMaxDate) and join(left join) both tables. You will have all data in the app, you can use IsMaxDate in the Set Analysis as a filter

Sum({<IsMaxDate = {1}>}Value)

Data:
LOAD
     ID,
     "Start time",
     "Completion time",
     Email,
     Name,
     "Please Scan or type your full name",
     "Please Scan or type your RF Gun ID",
     "Check-In Or Check-Out"
FROM [lib://RF Gun Check-In SharePoint]
(ooxml, embedded labels, table is Form1);

Left Join(Data)
LOAD
     ID,
     Timestamp(MAX(Date)) AS Date,
     1 AS IsMaxDate
Resident Data
Group By ID;

Regards,

Vitalii