Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Trying to get values from two tables

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

EmployeeIDEmployee Name
E001Employee-A
E002Employee-B
E003Employee-C

AccessRequest Table

EmployeeIDAccessCompletionDate
E001Jan 1, 2017
E001March 1, 2017
E001May 1, 2017
E002Jan 5, 2018
E002Feb 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.

9 Replies
kakani87
Specialist
Specialist

Hi Magesh ...May be this ?

RecentAccess.png

Anonymous
Not applicable
Author

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.

kakani87
Specialist
Specialist

Share some sample data in a excel file

shilpan
Partner Ambassador
Partner Ambassador

Are you looking for this? You can use firstsortedvalue function to get max date for each employee

community1.PNG

kakani87
Specialist
Specialist

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.

tamilarasu
Champion
Champion

Hi Saikishore,

FirstSortedValue(Date, -Date#(Replace(Mid(Date,6,15) &' '& Right(Date,4),'  ',' '),'MMM DD hh:mm:ss YYYY'))

Anonymous
Not applicable
Author

Thank you Tamil, but, the solution you provided just shows the date.  Here is the screen shot

tamilarasu
Champion
Champion

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.

Capture.PNG

isingh30
Specialist
Specialist

Chart Properties - Sort - Numeric Value - Descending

Thank you!