Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Get Customer Name on the Max Date based on the active record

Hello Guys,

I'm working on a requirement which seems straight forward but I'm unable to find any solution.

I have two tables 

a. Call Details: Contains call records with columns for A_Party(caller) and B_Party(receiver), Call Date

b. Customer Details: Contains Customer information such as name, MobileNo:, Date Inactive, Status.

The above two tables are connected using the MobileNo Key. The customer table contains multiple records for each Mobile Number with different customers. For Example:

Customer Details Table:

MobileNo:CustomerNameActivityDateStatus
12345John01/01/2018Active
9876Bill01/02/2018Expired
12345Dan09/04/2017Deactivated
12345Ronn12/03/2016Expired

 

Call Details Table:

Call_IDA_PartyB_party(MobileNo:)CallDatetimeLocation
1345671234525/02/2018 hh:mm:ssGeo Code
2234891234513/03/2017 hh:mm:ssGeo Code
3456781234523/02/2016 hh:mm:ssGeo Code

The above tables is joined on MobileNo: and B_Party(Mobile No:). We are interested to get the receiver details which is customer name depending upon this activity date in the customer details table.

Now the requirement is to get a table as below:

The customer name should be evaluated based on the Activity Date & Call Date. If the call date is greater than the activity date then the active customer should be evaluated as: if the call date between the period of the active status of the customer. 

Resultant Table:

Call_IDA_PartyB_PartyCall DateCustomerName
1345671234525/02/2018John
2234891234513/03/2017Dan
3456781234523/02/2016Ronn
     

 

Currently, my key is MobileNo: for this reason for each call record, I'm getting all customer names associated with the mobile no:. Is there any way to get a unique customer name as stated in the resultant table.

Is there any possibility to do this requirement on the front end as Call Details contains 560Million records and Customer Table contains 16Millions records any joins or generate missing dates might be resource intensive as the relationship is many to many between the tables.

Any suggestions would be appreciated.

 

1 Reply
Highlighted
Partner
Partner

You should consider a way to idenitfy a valid interval for which each customer row is valid. 

I do not match your deisired output completely, but you'll get the idea from my attached qvw. 

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes