Use only Overlapping Months

[Rehan]

I have a requirements where I  have multiple extract dates in a single dataset and each extract date has multiple months .

For example :

Extract Date = May 2019 has Data for May 2019, June 2019, July 2019 up to Dec 2019

Extract Date = Mar 2019 has Data for Mar 2019, Apr 2019 ,May 2019, June 2019 up to Oct 2019

The requirement is I need data for only overlapping months between the 2 extract dates . In this case the overlapping months will be May 2019 Until Oct 2019.

How can I accomplish this ? Idealy I would like to create some sort of a flag in the script to get the overlapping months between 2 extract Dates.

[TimvB]

First, load all possible Months from one source:

Table1:

NoConcatenate Load Distinct

MonthField

From YourSource1;

Second, load all possible months from the second source in a mapping table:

Table2_map:

Mapping Load Distinct

MonthField

’1’ as Flag

From YourSource2;

Third, create a flag if a month is in both sources:

FlagTable:

NoConcatenate Load

MonthField,

ApplyMap(‘Table2_map’,MonthField,’0’) as Flag

Resident Table1;

Drop Table Table1;

Fourth, use the FlagTable to filter the overlapping Months:

FinalTable:

NoConcatenate Load

MonthField,

Resident FlagTable

Where Flag = ‘1’;

Drop Table FlagTable;

Hope it helps!

[Rehan]

All the Extract dates are in the same source not different  sources. Its just one table

[TimvB]

Then get the Month fields from the same table:

Table:
NoConcatenate Load Distinct
MonthField1
From YourSource;
 
Table_map:
Mapping Load Distinct
MonthField2
‘1’ as Flag
From YourSource;
  
FlagTable:
NoConcatenate Load
MonthField1,
ApplyMap(‘Table_map’,MonthField1,’0’) as Flag
Resident Table;

Drop Table Table;
  
FinalTable:
NoConcatenate Load
MonthField1 as OverlappingMonths,
Resident FlagTable
Where Flag = ‘1’;

[Rehan]

I have more than 2 Extract dates, I gave 2 just as an example

[Rehan]

And Extract date is one field not 2 different fields

[Rehan]

Any more ideas?

[TimvB]

I do not understand the original question. Please provide a sample dataset and an example or clear description of the desired situation.

[Rehan]

Attached please find the sample data set with the desired Result

[TimvB]

It took quite some scripting to get the result, but I got it working. Attached you can find my Test.qvf.

The script I used:

//=== Extract the data
Data:
NoConcatenate LOAD
    date([Extract Date]) as [Extract Date],
    date([Forecast Date]) as [Forecast Date],
    Amount
FROM [lib://Desktop/Sample_Data.xlsx]
(ooxml, embedded labels, table is Data);

//=== Create a table with all the possible Extract Months
Extract_Months:
NoConcatenate Load Distinct
	month([Extract Date]) as [Extract Month]
Resident Data;

//=== Loop over the Extract Months
For i = 0 to (NoOfRows('Extract_Months') - 1);
	
    //=== Get the Extract Month
    Let vExtract_Month = Peek('Extract Month','$(i)','Extract_Months');
    
    //=== Get all possible Forecast Months of the Extract Month
    Forecast_Months_tmp:
	NoConcatenate Load Distinct
        Month([Forecast Date]) as [Forecast Month]
    Resident [Data]
    Where month([Extract Date]) = '$(vExtract_Month)';
    
	If i = 0 Then
    	//=== Create a new table for all Forecast Months
    	Rename Table Forecast_Months_tmp to Forecast_Months;
    Else
    	//=== Add the new found Forecast Months to the table
        //=== We need the RowNo() to keep double values
    	Concatenate(Forecast_Months) Load RowNo() as TempNr, * Resident Forecast_Months_tmp;
    	Drop Table Forecast_Months_tmp;
   	EndIf;
    
Next i;

Drop Table Extract_Months;

//=== Count how many times we found a every Forecast Month in distinctive Extract Months
Forecast_Months_Count:
NoConcatenate Load
    [Forecast Month],
    Count([Forecast Month]) as Count
Resident Forecast_Months
Group by [Forecast Month];
Drop Table Forecast_Months;

//=== Flag the Forecast Months that we found multiple times
Month_Flag_map:
Mapping Load
    [Forecast Month],
	'1' as Flag
Resident Forecast_Months_Count
Where Count >= '2';
Drop Table Forecast_Months_Count;

//=== Flag the records of the multiple Forecast Months
New_Data_tmp:
NoConcatenate Load
	[Extract Date],
	[Forecast Date],
	[Amount],
    ApplyMap('Month_Flag_map',month([Forecast Date]),'0') as Flag
Resident Data;
Drop Table [Data];

//=== Filter all records with the multiple Forecast Months
New_Data:
NoConcatenate Load
	[Extract Date],
	[Forecast Date],
	[Amount]
Resident New_Data_tmp
Where Flag = '1';
Drop Table New_Data_tmp;

Store New_Data into [lib://Desktop/Data.qvd];

Hope it helps!