topic Data restrict between start and end date in App Development

Data restrict between start and end date

paulwalker — Mon, 06 Nov 2023 06:00:29 GMT

Hi Community,

I have to restrict the data script level,

Table1:

Date	ID	Amount
31-12-2022	A1	18
10-04-2023	A1	23
13-02-2023	A1	55
14-01-2023	A1	10
31-12-2022	A2	18
10-04-2022	A2	45
13-02-2023	A2	23
14-01-2023	A2	90

Table2:

ID	Name	Start Date	End Date
A1	ABC	01-01-2023	20-06-2023
A2	BCA	01-01-2023	19-05-2023

Output Should be : based on start and end date I have to eliminate data script level (data should be display between the dates)

ex: for A1 data should be between 01-01-2023 and 20-06-2023

Date	ID	Amount
10-04-2023	A1	23
13-02-2023	A1	55
14-01-2023	A1	10
13-02-2023	A2	23
14-01-2023	A2	90

@tresesco, @MayilVahanan, @Kushal_Chawda, @marcus_sommer, @stevedark and experts can you please look into this

Thanks in Advance!

Re: Data restrict between start and end date

paulwalker — Mon, 06 Nov 2023 06:01:30 GMT

Experts, could you please help on it

Re: Data restrict between start and end date

marcus_sommer — Mon, 06 Nov 2023 07:45:15 GMT

There are various ways possible - quite simple would be:

ExistsFilter: load ID & '|' & date(StartDate + iterno() -1) as Filter
from Table2 while StartDate + iterno() -1 <= EndDate;

t: load * from Table1 where exists(Filter, ID & '|' & Date);

Re: Data restrict between start and end date

vincent_ardiet_ — Mon, 06 Nov 2023 09:22:16 GMT

With interval match for example:

SET DateFormat='DD-MM-YYYY';

Table1:
Load * inline
[
Date,ID,Amount
31-12-2022,A1,18
10-04-2023,A1,23
13-02-2023,A1,55
14-01-2023,A1,10
31-12-2022,A2,18
10-04-2022,A2,45
13-02-2023,A2,23
14-01-2023,A2,90];

Table2:
Load * inline
[
ID,Name,Start Date,End Date
A1,ABC,01-01-2023,20-06-2023
A2,BCA,01-01-2023,19-05-2023];

Inner Join (Table1) IntervalMatch (Date,ID)
Load [Start Date],[End Date], ID Resident Table2;

Drop Table Table2;