Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello. I am fairly new to QlikView. Here is my scenario.
I have two tables. Employee table and AccessRequest table. AccessRequest table will contain details about what Access Requests that an employee put and AccessCompletionDate field will have the date that a request has been completed for an employee.
I also want the latest date that an access request has been completed for an employee.
Let us say, Employee-A has access requested on Jan 1, 2018 and Feb 1, 2018. I want to pull the latest "DateCompleted".
Here is the pictorial representation of the scenario
Employee Table
EmployeeID | Employee Name |
---|---|
E001 | Employee-A |
E002 | Employee-B |
E003 | Employee-C |
AccessRequest Table
EmployeeID | AccessCompletionDate |
---|---|
E001 | Jan 1, 2017 |
E001 | March 1, 2017 |
E001 | May 1, 2017 |
E002 | Jan 5, 2018 |
E002 | Feb 10, 2018 |
I have created the dashboard scripts to load the data from the qvd.
All I want to do is in the dash board, I want to bring the EmployeeID, Employee Name and the most recent AccessCompletionDate.
Any help is highly appreciated.
Hi Magesh ...May be this ?
Thank you Kakani Saikishore. Now, introducing further complexity. My date values are with the time stamp. For example, here are some date values I have.
Mon, Apr 4 11:18:27 EDT 2016
Thu, Aug 25 12:04:54 EDT 2016
Thu, Feb 22 13:33:51 EDT 2018.
Now out of the above list, I was the latest date, which is Thu, Feb 22 13:33:51 EDT 2018..
Any help on this is highly appreciated.
Share some sample data in a excel file
Are you looking for this? You can use firstsortedvalue function to get max date for each employee
Dear Shilpan,
how to get Maxdate field from this type of data where Thu, Feb 22 13:33:51 EDT 2018 is the max date to show in date format
Mon, Apr 4 11:18:27 EDT 2016
Thu, Aug 25 12:04:54 EDT 2016
Thu, Feb 22 13:33:51 EDT 2018.
Hi Saikishore,
FirstSortedValue(Date, -Date#(Replace(Mid(Date,6,15) &' '& Right(Date,4),' ',' '),'MMM DD hh:mm:ss YYYY'))
Thank you Tamil, but, the solution you provided just shows the date. Here is the screen shot
Hi Magesh,
Sorry for the delayed response. Why are you taking completed date as dimension? If you take competed date as dimension then it will show all the dates. Your requirement was to show latest access date for specific employee, right? Have a look at the attached file.
Chart Properties - Sort - Numeric Value - Descending
Thank you!