if condition to looks 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

@PrashantSangle @sunny_talwar, @tresesco 

[MarcoWedel]

You want your Test1 table to only contain two rows specified by this condition?

Can you explain the logic for ID 2? Why are there dates loaded for this ID when today's date is well after  Date1?

[vikasshana]

yes my Test1 table has to contain only two rows specified by the condition.

we are getting the dates information from database and this is how it is defined.

[tresesco]

Can you explain why your 3rd record should by excluded and why 4th be included?

[vikasshana]

3rd record belongs to ID 1 and 4th record belongs to ID 2.