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 CodeTenancy_Commencement_DateOriginal_Lease_Expiry_DateSeventh_lease_commencement_dateSeventh_lease_expiry_date40401 2015-10-192016-10-18520902015-08-242016-08-23 Thanks Leaseexpiry.yxmd leaseexpiry.xlsx

Find last expiry date and pivot data

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

Leaseexpiry.yxmd

leaseexpiry.xlsx

Transformation

Preparation

Accepted answers

rahuls

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.

LeaseExpiry.yxzp

All comments

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

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.

LeaseExpiry.yxzp

jagjit_singh

Thanks Chris. Before i could attempt Rahul cracked it.

Jag

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703