topic Use only Overlapping Months in App Development

Use only Overlapping Months

Rehan — Tue, 04 Feb 2020 01:43:39 GMT

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.

Re: Use only Overlapping Months

TimvB — Tue, 04 Feb 2020 08:05:39 GMT

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!

Re: Use only Overlapping Months

Rehan — Tue, 04 Feb 2020 12:51:47 GMT

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

Re: Use only Overlapping Months

TimvB — Tue, 04 Feb 2020 14:49:41 GMT

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’;

Re: Use only Overlapping Months

Rehan — Tue, 04 Feb 2020 14:57:59 GMT

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

Re: Use only Overlapping Months

Rehan — Tue, 04 Feb 2020 14:59:43 GMT

And Extract date is one field not 2 different fields

Re: Use only Overlapping Months

Rehan — Thu, 06 Feb 2020 02:34:39 GMT

Any more ideas?

Re: Use only Overlapping Months

TimvB — Thu, 06 Feb 2020 06:24:08 GMT

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

Re: Use only Overlapping Months

Rehan — Thu, 06 Feb 2020 12:57:01 GMT

Attached please find the sample data set with the desired Result

Re: Use only Overlapping Months

TimvB — Thu, 06 Feb 2020 14:40:38 GMT

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!

Re: Use only Overlapping Months

Rehan — Thu, 06 Feb 2020 15:15:38 GMT

I am not able to open the message you sent

Re: Use only Overlapping Months

Rehan — Thu, 06 Feb 2020 15:17:18 GMT

The script I used:

Re: Use only Overlapping Months

TimvB — Thu, 06 Feb 2020 15:26:24 GMT

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.

Hope it helps!

Re: Use only Overlapping Months

Rehan — Thu, 06 Feb 2020 15:27:08 GMT

Can you pls attach the qvf again?

Re: Use only Overlapping Months

Rehan — Thu, 06 Feb 2020 15:34:34 GMT

Question why we are starting with NOConcatenate ?

Re: Use only Overlapping Months

TimvB — Thu, 06 Feb 2020 15:39:49 GMT

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.

Re: Use only Overlapping Months

Rehan — Thu, 06 Feb 2020 19:50:41 GMT

Attached please find the sample data and sample QVF

Re: Use only Overlapping Months

Rehan — Thu, 06 Feb 2020 19:51:58 GMT

Sample Data

Re: Use only Overlapping Months

sunny_talwar — Mon, 10 Feb 2020 12:56:35 GMT

Which rows do you expect to see flagged with duplicates?

Re: Use only Overlapping Months

Rehan — Mon, 10 Feb 2020 13:19:34 GMT

If I select Any 2 Extract Dates on the fron t ends I expect to see only the common months between those 2 selected dates.