if condition has to look into the previous records

[vikasshana]

Hi,

I've the below data in my app.

Test:
Load * Inline [
ID, Date, Date1
1, 8/1/2022, 7/31/2023
1, 8/1/2023, 7/31/2024
1, 10/11/2021, 7/31/022
2, 9/1/2013, 4/30/2020
];

And used below two if conditions as per the requirement

NoConcatenate
Test1:
Load DISTINCT ID,
if(date(today(),'M/D/YYYY')>Date AND date(today(),'M/D/YYYY')<Date1,Date,null()) as NewDate,
if(date(today(),'M/D/YYYY')>Date AND date(today(),'M/D/YYYY')<Date1,Date1,null()) as NewDate1
Resident Test;

Drop Table Test;

Received below output

ID	NewDate	NewDate1
1	8/1/2022	7/31/2023
1	-	-
2	-	-

My IF statement is loading only the records where today() is greater than Date and today() is less than Date1 but I'm looking for an IF statement where it has to give output as below

ID	NewDate	NewDate1
1	8/1/2022	7/31/2023
2	9/1/2013	4/30/2020

@tresesco 

[vikasshana]

Hi,

I've the below data in my app.

Test:
Load * Inline [
ID, Date, Date1
1, 8/1/2022, 7/31/2023
1, 8/1/2023, 7/31/2024
1, 10/11/2021, 7/31/022
2, 9/1/2013, 4/30/2020
];

And used below two if conditions as per the requirement

NoConcatenate
Test1:
Load DISTINCT ID,
if(date(today(),'M/D/YYYY')>Date AND date(today(),'M/D/YYYY')<Date1,Date,null()) as NewDate,
if(date(today(),'M/D/YYYY')>Date AND date(today(),'M/D/YYYY')<Date1,Date1,null()) as NewDate1
Resident Test;

Drop Table Test;

Received below output

ID	NewDate	NewDate1
1	8/1/2022	7/31/2023
1	-	-
2	-	-

My IF statement is loading only the records where today() is greater than Date and today() is less than Date1 but I'm looking for an IF statement where it has to give output as below

ID	NewDate	NewDate1
1	8/1/2022	7/31/2023
2	9/1/2013	4/30/2020

@tresesco 

[SchalkF]

Hi,

If I am understanding your IF statements correctly in normal tongue it checks if today is between [date] and [date1].

You will never receive the following record in your dataset:

ID	NewDate	NewDate1
2	9/1/2013	4/30/2020

This is because today() (9/9/2022) is not between 9/1/2013 and 4/30/2020.

You are also receiving the following 2 records because of the else in your IF statement which sets extra DISTINCT records in your data set (If today is not in between [Date] and [Date1], it should be Null):

ID	NewDate	NewDate1
1	-	-
2	-	-

Maybe if you give more detail into why you are using today() in your IF  statement or what the business requirement is, the community can assist in giving a different solution to your requirement.

Kind regards

[vikasshana]

Business requirement is if we have more than one date for an ID it has to take the date logic (today() is greater than Date and today() is less than Date1) and also in some cases from some of the ID's there will be only one date and in that case if date and date1 is less than today() we have to display that date.