Mapping Two Tables Based on Date logic

[anuradhaa]

I'm Using Two Sals to get Data from Two different data sources,

One Table i have,

DateStarted, Name , Rate  ( Names are repeating.)

Another Table i have,

Name,Cost, Date (Here also Names are repeating.)

I need to get ,

Name, Rate , and Cost and Group It by DateStartedMonth in Pivot Table.

Note-

Logic is Date of second table need to map with Latest Started Date Passed,

Say Name1 has below started Dates,

2015/05/26

2015/08/30

2015/11/23

And Table two Dates for Name1 as

2015/09/30

Then That record has to map with 2015/08/30 Date.

Thanks,

[sunny_talwar]

Can you provide your current data for the two tables in a Excel file. I am not sure how the data looks like. But from what I understand you might need a Interval Match

[swuehl]

You could consider creating a date interval from your single DateStarted field:

Creating a Date Interval from a Single Date

Then use e.g. IntervalMatch to map your Date field values into the Rate intervals using Name as a key.

[anuradhaa]

See Attached xsl for data

[anuradhaa]

See attached for data and the results that i need

[anuradhaa]

See attached for data and what i want

[sunny_talwar]

The dates on the SQL2 tab of the Excel contain 2014 information vs. 2015 + information in SQL1 tab. How do you intend to visualize that? Can you input values into the output you are expecting to see?

[anuradhaa]

First Priority Goes to Tab1(Sql1). If there are no records for the dates in Tab2(Sql2) ignore those

[swuehl]

Maybe like this using above referenced approach:

SET DateFormat  = 'MM/DD/YYYY';


Tmp_Rates:
LOAD DATE_Started,
    Name,
    LEASE_RATE,
    Customer
FROM

(biff, embedded labels, table is SQL1$);


Let vEndTime =MakeDate(2020,12,31);


Rates:
LOAD Customer, LEASE_RATE, DATE_Started, Name,
  Date(If(Customer=Peek(Customer),Peek(DATE_Started)-0.00000001, $(#vEndTime))) as DATE_End
Resident Tmp_Rates
Order By  Name, Customer, DATE_Started Desc;


Drop Table Tmp_Rates;






Cost:
LOAD Nmae as Name,
    year,
    month,
    MakeDate(year, month) as Date,
    COST
FROM

(biff, embedded labels, table is SQL2$);


JOIN
IntervalMatch (Date, Name)
LOAD DATE_Started, DATE_End, Name Resident Rates;

Customer	Date	Sum(COST)	Sum(LEASE_RATE)
		855	5021
Cus A	08/01/2015	855	952
Cus A		0	2268
CUSB		0	797
CusB		0	1004

[sunny_talwar]

Try the attached: