Find last expiry date and pivot data

[jagjit_singh]

Hi All,

In the attached excel sheet, i like to idenitfy the max expiry date, associated commencement date and finally display the result in a pivot as below. I have attached the sample workflow to illustrate the desired result.

Desired Result:

Tenant Code	Tenancy_Commencement_Date	Original_Lease_Expiry_Date	Seventh_lease_commencement_date	Seventh_lease_expiry_date
40401			2015-10-19	2016-10-18
52090	2015-08-24	2016-08-23

Thanks

[chris_love]

Hi Jagjit,

I won't give you a module as it's worth experimenting with this one to
learn yourself but here are the steps I'd follow:


1. Filter to find Questions containing "Expiry Date"

2. Use summarise to find the max expiry date per Tenant Code

3. match (using a join tool) to find which records have that maximum expiry
date

4. this will give you the question, e.g. Fifth Expiry Date, so replace the
"expiry date" with "commencement date" in a formula to create a field you
can then join to

5. join again to find the commencement date

6. union the records from (4) and (5)

7. cross tab using a crosstab tool


have a go, it will be fun I promise. If you have a go and are still stuck
come back and I can provide a module.


Chris

[rahuls · Accepted solution]

Hey Jag,

This should help you, its basically what Chris explained plus joining the two dates using a token. You will know when you open that workflow.

It seems that this is not the data at source and I believe the approach can be improved from the source. 

For now, try to use this workflow, should solve your purpose. It was a good workout. :)

[jagjit_singh]

Thanks Chris. Before i could attempt Rahul cracked it.

Jag