topic Trying to get values from two tables in QlikView

Trying to get values from two tables

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Trying to get values from two tables

kakani87 — Sat, 24 Feb 2018 12:16:34 GMT

Hi Magesh ...May be this ?

Re: Trying to get values from two tables

Anonymous — Mon, 26 Feb 2018 19:40:07 GMT

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.

Re: Trying to get values from two tables

kakani87 — Tue, 27 Feb 2018 04:59:17 GMT

Share some sample data in a excel file

Re: Trying to get values from two tables

shilpan — Tue, 27 Feb 2018 05:20:14 GMT

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

Re: Trying to get values from two tables

kakani87 — Tue, 27 Feb 2018 05:44:50 GMT

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.

Re: Trying to get values from two tables

tamilarasu — Tue, 27 Feb 2018 06:02:33 GMT

Hi Saikishore,

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

Re: Trying to get values from two tables

Anonymous — Tue, 27 Feb 2018 16:32:32 GMT

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

Re: Trying to get values from two tables

tamilarasu — Wed, 28 Feb 2018 18:15:18 GMT

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.

Re: Trying to get values from two tables

isingh30 — Wed, 28 Feb 2018 18:58:32 GMT

Chart Properties - Sort - Numeric Value - Descending

Thank you!