Filter loaded tables

[keithboruta]

Hello all,

I am trying to calculate the latest date per Material, Global Supplier and Site combination (can be multiple records for the same Material/Global Supplier/Site) and then only load in those that are the latest date.

What works: Finding the latest date based on Material/Global Supplier/Site.

What doesn't work: Filtering the records to load based on latest date (only load items that are the latest date).

What I have:

[base]:
Load *

//FROM [lib://Downloads/Top Spend Analysis Ford GEN 3 RFQ.xlsx]
FROM [lib://Downloads/CY19 9+3 SEP SINGER @ 100419.xlsx]
(ooxml, embedded labels, table is [SINGER @ September ME]);

[Trimmed]:
Load Distinct
[Material],
[Global Supplier],
[Site],
Date#(Replace([Effectivity Period],' ',''),'MM/YYYY') as [Effective Date]
Resident base;

[Latest Price]:
Load Distinct
[Material],
[Global Supplier],
[Site],
Date(Max([Effective Date]),'MM/YYYY') as [Latest Effective Date]
Resident Trimmed
Group by Material,[Global Supplier],[Site];

[Mergy1]:
Load * Resident base;
Load * Resident Trimmed;
Load * Resident [Latest Price];

[Mergy2]:
Load * Resident Mergy1
Where [Effective Date] = [Latest Effective Date];

DROP table base, Trimmed, [Latest Price], Mergy1

Thoughts???

[keithboruta · Accepted solution]

I ended up solving my own issue 🙂

[Mergy1]:
Load * Resident base;
//Load * Resident Trimmed;
join Load * Resident [Latest Price];
join Load * Resident [Trimmed];

NoConcatenate

[Mergy2]:
Load * Resident Mergy1
Where [Effective Date] = [Latest Effective Date];

Drop table base, Trimmed, Mergy1, [Latest Price];

The JOINS in Mergy1 Fixed the issue. Then I dropped the tables I no longer needed and the report worked!