Interval Match using Dates

[vmurugananthan]

Hi,

Below is my problem statement

Problem Statement : I have a master data set which has 10 distinct customers. I have another data set (called priority data set) which has 5 among the 10 customers from the master data set. This is basically to flag these 5 customers as priority customers in the master data set. I do this by left joining based on customer number and setting a flag in the master data set.

My requirement is that from the master data set , if the Report Date for a customer is higher than the End Date from priority Data set for that customer, then I need to show the data only until that End date for that customer.Ex: If the End date for a customer is 20-08-2020, then i need to show the fileNumbers for that customer only before this date.

The master data set looks like below

CustomerNumber	Report Date	FileNumber
1	10-01-2020	100
1	31-08-2020	200
2	02-02-2020	300
3	20-02-2020	400
3	27-03-2020	500
4	04-04-2020	600
5	05-05-2020	700
6	06-06-2020	800
7	07-06-2020	900
8	07-07-2020	1000
9	24-08-2020	1100
10	27-08-2020	1200

Priority data set: 

CustomerNumber	Start Date	End Date
1	01-01-2010	20-08-2020
2	01-01-2010	01-01-2030
3	01-01-2010	01-01-2030
6	01-01-2010	01-01-2030
7	01-01-2010	01-01-2030

My Output when i set the priority customer flag, should ideally look like:

CustomerNumber	Report Date	FileNumber
1	10-01-2020	100
2	02-02-2020	300
3	20-02-2020	400
3	27-03-2020	500
6	06-06-2020	800
7	07-06-2020	900

i.e, one file number (200) for customer 1 has been removed because the Report Date is greater than the End  Date. 

Any Help on this would be really helpful.

@vamsee @Miguel_Angel_Baeyens 

Thanks in Advance !

[Taoufiq_Zarra]

@vmurugananthan,

If I understod correctly , one solution :

Data:

LOAD * INLINE [
    CustomerNumber, Start Date, End Date
    1, 01-01-2010, 20-08-2020
    2, 01-01-2010, 01-01-2030
    3, 01-01-2010, 01-01-2030
    6, 01-01-2010, 01-01-2030
    7, 01-01-2010, 01-01-2030
];


left join

LOAD * INLINE [
    CustomerNumber, Report Date, FileNumber
    1, 10-01-2020, 100
    1, 31-08-2020, 200
    2, 02-02-2020, 300
    3, 20-02-2020, 400
    3, 27-03-2020, 500
    4, 04-04-2020, 600
    5, 05-05-2020, 700
    6, 06-06-2020, 800
    7, 07-06-2020, 900
    8, 07-07-2020, 1000
    9, 24-08-2020, 1100
    10, 27-08-2020, 1200
] ;


Final:
noconcatenate

load * resident Data where [Report Date]<=[End Date];

drop table Data;

output :

[vmurugananthan · Accepted solution]

Hi,

I found the Answer myself. I had used extended interval match. Something like below:

Left Join (MasterData)

IntervalMatch(ReportDate, CustomerNumber)

Load StartDate,

EndDate,

CustomerNumber

Resident PriorityDataset;

Left Join (Master Data)

Load *,

'1' as PriorityIndicator

Resident PriorityDataset;

This works for me. Thus closing this question. 

[vmurugananthan]

This would remove all the other Customers right ? But i have 2 views where i show the priority customers and in another i show all customers

[Taoufiq_Zarra]

@vmurugananthanyou can add a Flaglike :

Data:

LOAD * INLINE [
    CustomerNumber, Start Date, End Date
    1, 01-01-2010, 20-08-2020
    2, 01-01-2010, 01-01-2030
    3, 01-01-2010, 01-01-2030
    6, 01-01-2010, 01-01-2030
    7, 01-01-2010, 01-01-2030
];


left join

LOAD * INLINE [
    CustomerNumber, Report Date, FileNumber
    1, 10-01-2020, 100
    1, 31-08-2020, 200
    2, 02-02-2020, 300
    3, 20-02-2020, 400
    3, 27-03-2020, 500
    4, 04-04-2020, 600
    5, 05-05-2020, 700
    6, 06-06-2020, 800
    7, 07-06-2020, 900
    8, 07-07-2020, 1000
    9, 24-08-2020, 1100
    10, 27-08-2020, 1200
] ;


Final:
noconcatenate

load *,if([Report Date]<=[End Date],1,0) as Flag resident Data;

drop table Data;

and you can use flag as list :

or by Set analysis : {<Flag={1}>}  to show the priority customers