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.
//=== 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];
It took some scripting effort to get the desired result. The script below will work: You only need to change your data connection. Attached is the Test.qvf.
//=== 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];
The Test.qvf is attached to my most recent comment. I use NoConcatenate Load such that tables are never concatenated by accident. I do not know the rest of your script, so to be sure that this part won’t conflict with your script, I used NoConcatenate.
