Data arrangement without using looping

[vvira1316]

Hi,

I've two tables as shown below.

Table1
ID1	ID2	Cycle Start Date	Cycle End Date	Pay Date
123	1	7/14/18	8/15/18	9/1/18
123	1	8/16/18	9/14/18	10/1/18
123	1	9/15/18	10/15/18	11/1/18
123	1	10/16/18	11/15/18	12/1/18
123	1	11/16/18	12/14/18	1/1/19
123	1	12/15/18	1/15/19	2/1/19

Table2
ID1	ID2	Flag Start Date	Flag End Date
123	1	7/18/2018	7/20/2018
123	1	7/22/2018	8/5/2018
123	1	8/11/2018	8/17/2018
123	1	9/14/2018	9/14/2018
123	1	9/15/2018	9/15/2018
123	1	9/17/2018	12/1/2018
123	1	9/17/2018	10/15/2018
123	1	9/17/2018	10/16/2018
123	1	9/17/2018	11/8/2018
123	1	9/17/2018	11/15/2018
123	1	7/18/2018	11/20/2018

The logic condition that will provide two data point for each line of Table 2 using fields from table 1 is provided below and it will provide values for each rows of Table2 as follows

First Pay Date Impacted	Last Pay Date Impacted
Flag Start Date >= Cycle Start Date AND Flag Start Date <= Cycle End Date	Flag End Date >= Cycle Start Date AND Flag End Date <= Cycle End Date	All Pay Date Months Between First and Last Pay Date Impacted
9/1/2018	9/1/2018	9/1/2018
9/1/2018	9/1/2018	9/1/2018
9/1/2018	10/1/2018	9/1/2018 10/1/2018
10/1/2018	10/1/2018	10/1/2018
11/1/2018	11/1/2018	11/1/2018
11/1/2018	1/1/2019	11/1/2018 12/1/2018 1/1/2019
11/1/2018	11/1/2018	11/1/2018
11/1/2018	12/1/2018	11/1/2018 12/1/2018
11/1/2018	12/1/2018	11/1/2018 12/1/2018
11/1/2018	12/1/2018	11/1/2018 12/1/2018
9/1/2018	1/1/2019	9/1/2018 10/1/2018 11/1/2018 12/1/2018 1/1/2019

Expected output

Values of "All Pay Date Months Between First and Last Pay Date Impacted" of each row from above table and table2 will provide Pay Date Column in output

ID1	ID2	Flag Start Date	Flag End Date	Pay Date
123	1	7/18/2018	7/20/2018	9/1/2018
123	1	7/22/2018	8/5/2018	9/1/2018
123	1	8/11/2018	8/17/2018	9/1/2018
123	1	8/11/2018	8/17/2018	10/1/2018
123	1	9/14/2018	9/14/2018	10/1/2018
123	1	9/15/2018	9/15/2018	11/1/2018
123	1	9/17/2018	12/1/2018	11/1/2018
123	1	9/17/2018	12/1/2018	12/1/2018
123	1	9/17/2018	12/1/2018	1/1/2019
123	1	9/17/2018	10/15/2018	11/1/2018
123	1	9/17/2018	10/16/2018	11/1/2018
123	1	9/17/2018	10/16/2018	12/1/2018
123	1	9/17/2018	11/8/2018	11/1/2018
123	1	9/17/2018	11/8/2018	12/1/2018
123	1	9/17/2018	11/15/2018	11/1/2018
123	1	9/17/2018	11/15/2018	12/1/2018
123	1	7/18/2018	11/20/2018	9/1/2018
123	1	7/18/2018	11/20/2018	10/1/2018
123	1	7/18/2018	11/20/2018	11/1/2018
123	1	7/18/2018	11/20/2018	12/1/2018
123	1	7/18/2018	11/20/2018	1/1/2019

I don't want to use for loop in the script.

Any help/guidance will be very much appreciated.

BR,

Vijay

[Peter_Cammaert · Accepted solution]

Something like this:

LEFT JOIN (Table2)
INTERVALMATCH ([Flag Start Date], ID1, ID2)
LOAD [Cycle Start Date], [Cycle End Date], ID1, ID2
RESIDENT Table1;

LEFT JOIN (Table2)
LOAD ID1, ID2, [Cycle Start Date], [Cycle End Date], [Pay Date] AS [First Pay Date Impacted]
RESIDENT Table1;

DROP Fields [Cycle Start Date], [Cycle End Date] FROM Table2;

LEFT JOIN (Table2)
INTERVALMATCH ([Flag End Date], ID1, ID2)
LOAD [Cycle Start Date], [Cycle End Date], ID1, ID2
RESIDENT Table1;

LEFT JOIN (Table2)
LOAD ID1, ID2, [Cycle Start Date], [Cycle End Date], [Pay Date] AS [Last Pay Date Impacted]
RESIDENT Table1;

DROP Fields [Cycle Start Date], [Cycle End Date] FROM Table2;

DROP Table Table1;

Table3:
LOAD ID1, ID2, [Flag Start Date], [Flag End Date],
     AddMonths([First Pay Date Impacted], IterNo()-1) AS [Pay Date]
RESIDENT Table2
WHILE AddMonths([First Pay Date Impacted], IterNo()-1) <= [Last Pay Date Impacted];

DROP Table Table2;

produces output like that:

[vvira1316]

Hi Peter,

Thanks. I'll check it out.

BR,

Vijay

[vvira1316]

Hi Peter,

It is mostly working but there are couple of data scenario where it is not providing records that I would expect. I'm attaching my sample data and qvw file for your reference.

In one of the two scenario not sure it is interval match or addmonths is causing the issue. This is when year of Flag Start Date and Flag End Date are different. (ID1 = 23106, 23107, 23108, 23109)

The other scenario is when Flag Start Date = Cycle End Date (ID1 = 24465) (Result Verification tab in the attached sample file)

Thanks & Regards,

Vijay

it is the interval match that is causing the problem

I inserted a record with 12/1/2017 Pay Date for 23106, 23107, 23108, 23109 and it gave me missing records that I was looking for.

inserting records in table 1 is not the solution, so we need to see how we can resolve.

[vvira1316]

Hi,

I would like to change the interval check (condition logic)

Cycle Start Date to fall within Flag Start Date and Flag End Date to retrieve each Pay Date and Similarly Cycle End Date to fall within Flag Start Date and Flag End Date to retrieve each Pay Date thus it will/should provide intermediate table as follows

Intermediate Table
ID1	ID2	ID3	ID4	Flag Start Date	Flagged Start By	Flag End Date	Flagged End By	Pay Date	Coming From Not a Column in the intermediate table
23106	7522223	3	11/2/17 4:18 PM	E1024496	1/17/18 11:05 AM	E3069223	1/1/2018	Cycle Start Date Check
23106	7522223	3	11/2/17 4:18 PM	E1024496	1/17/18 11:05 AM	E3069223	2/1/2018	Cycle Start Date Check
23106	7522223	3	11/2/17 4:18 PM	E1024496	1/17/18 11:05 AM	E3069223	3/1/2018	Cycle Start Date Check
23106	7522223	3	11/2/17 4:18 PM	E1024496	1/17/18 11:05 AM	E3069223	1/1/2018	Cycle End Date Check
23106	7522223	3	11/2/17 4:18 PM	E1024496	1/17/18 11:05 AM	E3069223	2/1/2018	Cycle End Date Check
23106	7522223	3	1/17/18 11:27 AM	E3069223	1/18/18 11:59 AM	E3069223	3/1/2018	Cycle Start Date Check
23106	7522223	3	1/17/18 11:27 AM	E3069223	1/18/18 11:59 AM	E3069223	3/1/2018	Cycle End Date Check

Final Table from Intermediate Table - This will be distinct records from Intermediate Table
ID1	ID2	ID3	ID4	Flag Start Date	Flagged Start By	Flag End Date	Flagged End By	Pay Date
23106	7522223	3	11/2/17 4:18 PM	E1024496	1/17/18 11:05 AM	E3069223	1/1/2018
23106	7522223	3	11/2/17 4:18 PM	E1024496	1/17/18 11:05 AM	E3069223	2/1/2018
23106	7522223	3	11/2/17 4:18 PM	E1024496	1/17/18 11:05 AM	E3069223	3/1/2018
23106	7522223	3	1/17/18 11:27 AM	E3069223	1/18/18 11:59 AM	E3069223	3/1/2018

[vvira1316]

Hi Peter,

Your code definitely helped me. I'm thankful for your help. The other issue was related to particular data scenario. I was able to code differently to create those records. Combining records from your code and other logic provided me all the records I was looking for.

I'll be marking your solution correct as it is providing the output as per expectation of original request.

BR,

Vijay

[Peter_Cammaert]

Well done!

Sorry for the delay, but I didn't have much free time to investigate your remarks. I'm glad you were able to adapt the generic logic to your situation.