topic Re: Interval Match using Dates in QlikView

Interval Match using Dates

vmurugananthan — Sat, 16 Nov 2024 00:03:49 GMT

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 !

Re: Interval Match using Dates

Taoufiq_Zarra — Fri, 28 Aug 2020 08:35:02 GMT

@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 :

Re: Interval Match using Dates

vmurugananthan — Fri, 28 Aug 2020 08:40:43 GMT

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.

Re: Interval Match using Dates

vmurugananthan — Fri, 28 Aug 2020 08:44:31 GMT

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

Re: Interval Match using Dates

Taoufiq_Zarra — Fri, 28 Aug 2020 11:05:38 GMT

@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