Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
Highlighted
New Contributor

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
Valued Contributor

Re: Trying to get values from two tables

Hi Magesh ...May be this ?

RecentAccess.png

Highlighted
New Contributor

Re: Trying to get values from two tables

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.

Highlighted
Valued Contributor

Re: Trying to get values from two tables

Share some sample data in a excel file

Highlighted
Luminary
Luminary

Re: Trying to get values from two tables

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

community1.PNG

Highlighted
Valued Contributor

Re: Trying to get values from two tables

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.

Highlighted

Re: Trying to get values from two tables

Hi Saikishore,

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

Highlighted
New Contributor

Re: Trying to get values from two tables

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

Highlighted

Re: Trying to get values from two tables

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

Highlighted
Valued Contributor

Re: Trying to get values from two tables

Chart Properties - Sort - Numeric Value - Descending

Thank you!