Advanced Concatenate Based on Date

[#1]

So I have two inline tables. I want to create a table which concatenate these two only if the date field of the first one is one month before the date field of last one. Example:

Main:

LOAD * INLINE [

    Key, DATE, Qty

    1, 10/1/2015, 100

    2, 10/1/2015, 200

    3, 10/1/2015, 300

    4, 10/1/2015, 400

    5, 10/1/2015, 500

];

NoConcatenate

temp_table:

LOAD * INLINE [

    Key, DATE2, Qty

    1, 11/1/2015, 10

    2, 11/1/2015, 20

    6, 11/1/2015, 30

    7, 11/1/2015, 40

    8, 11/1/2015, 50

    9, 12/1/2015, 60

];

temp:

LOAD

Key as KeyInTemp,

DATE as DATEInTemp

Resident Main;

Concatenate(temp)

LOAD

Key as KeyInTemp

Resident temp_table Where Date(DATE#(DATEInTemp,'MM/DD/YYYY')) = AddMonths(Date(DATE#(DATE2,'MM/DD/YYYY')),-1);

MyResult:

LOAD Distinct

*

Resident temp;

So the expected output is:

Key     DATEInTemp

1          10/1/2015

2          10/1/2015

3          10/1/2015

4          10/1/2015

5          10/1/2015

6          10/1/2015

7          10/1/2015

8          10/1/2015

***9 will not be added because date is two month ahead***

Thanks in advance guys

[Gysbert_Wassenaar]

Maybe like this:

Main:

LOAD * INLINE [

    Key, DATE, Qty

    1, 10/1/2015, 100

    2, 10/1/2015, 200

    3, 10/1/2015, 300

    4, 10/1/2015, 400

    5, 10/1/2015, 500

];

LET vDate = num(date#(peek('DATE'),'M/D/YYYY'));

LOAD Key, DATE2 as DATE, Qty INLINE [

    Key, DATE2, Qty

    1, 11/1/2015, 10

    2, 11/1/2015, 20

    6, 11/1/2015, 30

    7, 11/1/2015, 40

    8, 11/1/2015, 50

    9, 12/1/2015, 60

]

WHERE num(AddMonths(Date#(DATE2,'M/D/YYYY'),-1)) = $(vDate);

;