Range help

[Navya08]

Hi, is there any way to compare to files using a date range?

like: i have 2 file having jan and feb data.

I want to compare previous month with current: i.e Jan with feb and want to identify all new entries and then compare these entries with a range.

example: comparing jan with feb we will see new entries in feb month. then we should compare this start date range (02/01-02/29) and for jan (01/01-01/31  and if not in range it will show as "DISCREPANCY/ALERT"

i might approach by joining the two files and see the remaining data in r anchor or j anchor and then putting the criteria. not sure though.

[binuacs · Accepted solution]

@Navya08 One way of doing this. Make sure the month is present in the input file name

[Navya08]

@binuacs Just one more thing, if i have timestamp IN my date field- how can i remove that- i am trying using  formula tool- Datetimeformat ([startdate], "month"), %Y-%m-%d")

Its not showing correct output.

Eg:2024-01-10 00:00:00

[binuacs]

@Navya08 use the function toDate() and change the data type to Date 

toDate([startdate])

[Navya08]

@binuacs you created  diff columns start date and end date

what if i use single formula tool and write IIFstatement  [start date]>="2024-01-01" and [start date]<= "2024-01-31","Discrepnacy","no discrepancy")

why is the output wrong?

[binuacs · Accepted solution]

@Navya08 you can write all in one single formula as well, I created two seperate columns to avoid complexity and it is easy to understand, also to make it dynamic I used the file name as the month and from that I derived the first of the month and last of the month date for the formula.

For your formula you are checking the date range between first of Jan and end of Jan, I think you need to inter change the end result like below, if it is falling in the range then no descripency else descripency

IIF([start date]>="2024-01-01" and [start date]<= "2024-01-31","No Discrepnacy","discrepancy")