Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
All the Extract dates are in the same source not different sources. Its just one table
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’;
I have more than 2 Extract dates, I gave 2 just as an example
And Extract date is one field not 2 different fields
Any more ideas?
I do not understand the original question. Please provide a sample dataset and an example or clear description of the desired situation.
Attached please find the sample data set with the desired Result
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!