cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Rini_Kar
Level II

To fetch records between two date range

Hi, 

I have the following data table with the first 4 columns being the input and last 2 being the output required. I tried to join two tables to combine column 1-3 and column 4 by matching ID = ID and Adm.Date = Ref.Date. Now I want two output columns (Adm. Date_1 and Dis_Date_1) as shown below. I need the Adm.Date and Dis.Date populated for all the Ref.Date between range of Adm.Date and Dis.Date and missing value for those which does not fit into the date range. I was able to get respective Adm. Date and Dis.Date using if and lag function only for next following record and not for all the records in that range. Please let me know if there's any way to find an appropriate solution to this problem.

 

Thanks in advance.

 

IDAdm. DateDis.DateRef.DateAdm.Date_1Dis.Date_1
122/12/201415/12/20142/12/20142/12/201415/12/2014
12  3/12/20142/12/201415/12/2014
12  6/12/20142/12/201415/12/2014
13  10/1/2015  
136/1/201625/1/20166/1/20166/1/201625/1/2016
13  10/1/20166/1/201625/1/2016
13  11/1/20166/1/201625/1/2016
13  12/1/20166/1/201625/1/2016
1 ACCEPTED SOLUTION

Accepted Solutions
cwillden
Super User (Alumni)

Re: To fetch records between two date range

You can accommodate that pretty easily with some logical checks on ID:

dt = Current Data Table();

ad_date_temp = .;
dis_date_temp = .;
id_temp = :ID[1];

for(i=1,i<=N Row(dt), i++,
	//Only update these vars if current row has new values for them
	
	If(!Is Missing(:Name("Adm. Date")[i]),
		//Execute if new Adm. Date
			ad_date_temp = :Name("Adm. Date")[i];
			dis_date_temp = :Name("Dis. Date")[i];
			id_temp = :ID[i],
	);
	
	If(:Name("Ref.Date")[i] >= ad_date_temp & :Name("Ref.Date")[i] <= dis_date_temp & :ID[i] == id_temp,
		//Execute if Ref Date is in the current range
			:Name("Adm.Date_1")[i] = ad_date_temp;
			:Name("Dis.Date_1")[i] = dis_date_temp
		,
		//Execute if Ref Date is not in the current range
			:Name("Adm.Date_1")[i] = .;
			:Name("Dis.Date_1")[i] = .
	);
);
-- Cameron Willden

View solution in original post

4 REPLIES 4
cwillden
Super User (Alumni)

Re: To fetch records between two date range

Here's a solution that worked on your test case by looping through the rows in the table.  Let me know if you need help understanding the code.

dt = Current Data Table();

ad_date_temp = .;
dis_date_temp = .;

for(i=1,i<=N Row(dt), i++,
	//Only update these vars if current row has new values for them
	ad_date_temp = If(IsMissing(:Name("Adm. Date")[i]),ad_date_temp, :Name("Adm. Date")[i]);
	dis_date_temp = If(IsMissing(:Name("Dis. Date")[i]),dis_date_temp, :Name("Dis. Date")[i]);
	
	Show(i, ad_date_temp, dis_date_temp);
	If(:Name("Ref.Date")[i] >= ad_date_temp & :Name("Ref.Date")[i] <= dis_date_temp,
		//Execute if Ref Date is in the current range
			:Name("Adm.Date_1")[i] = ad_date_temp;
			:Name("Dis.Date_1")[i] = dis_date_temp
		,
		//Execute if Ref Date is not in the current range
			:Name("Adm.Date_1")[i] = .;
			:Name("Dis.Date_1")[i] = .
	);
);
-- Cameron Willden
Rini_Kar
Level II

Re: To fetch records between two date range

Hi Wilden,

Thank you for your solution. 

I want the Ref.Date to be in the respective date range for that particular ID. Lets say for row 4 (ID 13), Ref.Date is between Adm.Date and Dis.Date of ID 12, so it should be "." instead of taking values of ID 12. Ideally, date should be populated based on ID and not on the previous Adm.Date and Dis.Date record available. I am not sure if this script will take care of this requirement. Appreciate your help in this. Thanks.

 

Rini

  

IDAdm. DateDis.DateRef.DateAdm.Date_1Dis.Date_1
122/12/201415/12/20142/12/20142/12/201415/12/2014
12  3/12/20142/12/201415/12/2014
12  6/12/20142/12/201415/12/2014
13  13/12/2014  
13  10/1/2015  
136/1/201625/1/20166/1/20166/1/201625/1/2016
13  10/1/20166/1/201625/1/2016
13  11/1/20166/1/201625/1/2016
13  12/1/20166/1/201625/1/2016

 

 

 

 

cwillden
Super User (Alumni)

Re: To fetch records between two date range

You can accommodate that pretty easily with some logical checks on ID:

dt = Current Data Table();

ad_date_temp = .;
dis_date_temp = .;
id_temp = :ID[1];

for(i=1,i<=N Row(dt), i++,
	//Only update these vars if current row has new values for them
	
	If(!Is Missing(:Name("Adm. Date")[i]),
		//Execute if new Adm. Date
			ad_date_temp = :Name("Adm. Date")[i];
			dis_date_temp = :Name("Dis. Date")[i];
			id_temp = :ID[i],
	);
	
	If(:Name("Ref.Date")[i] >= ad_date_temp & :Name("Ref.Date")[i] <= dis_date_temp & :ID[i] == id_temp,
		//Execute if Ref Date is in the current range
			:Name("Adm.Date_1")[i] = ad_date_temp;
			:Name("Dis.Date_1")[i] = dis_date_temp
		,
		//Execute if Ref Date is not in the current range
			:Name("Adm.Date_1")[i] = .;
			:Name("Dis.Date_1")[i] = .
	);
);
-- Cameron Willden
Rini_Kar
Level II

Re: To fetch records between two date range

Thank you for the solution. It worked perfectly.