Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
vmurugananthan
Contributor II
Contributor II

Interval Match using Dates

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

CustomerNumberReport DateFileNumber
110-01-2020100
131-08-2020200
202-02-2020300
320-02-2020400
327-03-2020500
404-04-2020600
505-05-2020700
606-06-2020800
707-06-2020900
807-07-20201000
924-08-20201100
1027-08-20201200

 

Priority data set: 

CustomerNumberStart DateEnd Date
101-01-201020-08-2020
201-01-201001-01-2030
301-01-201001-01-2030
601-01-201001-01-2030
701-01-201001-01-2030

 

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

CustomerNumberReport DateFileNumber
110-01-2020100
202-02-2020300
320-02-2020400
327-03-2020500
606-06-2020800
707-06-2020900

 

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 !

Labels (1)
1 Solution

Accepted Solutions
vmurugananthan
Contributor II
Contributor II
Author

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. 

View solution in original post

4 Replies
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 :

Capture.PNG

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
vmurugananthan
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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 :

Taoufiq_ZARRA_0-1598612645810.png

 

Taoufiq_ZARRA_1-1598612661495.png

 

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

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉