Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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.
See Attached xsl for data
See attached for data and the results that i need
See attached for data and what i want
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?
First Priority Goes to Tab1(Sql1). If there are no records for the dates in Tab2(Sql2) ignore those
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 |
Try the attached: