topic Re: Log Date when Report/Query adds new values in Getting Data In

How to log date when report/query adds new values?

PTC_ — Wed, 08 Mar 2023 20:35:29 GMT

I have created a Report with a Query that updates a list of NAMES on CSV file.
If the NAMES field have empty strings or null values, the Query will try to get the NAME from another field and add it to NAMES. Something like this:

NAMES	ADDED_ON_INDEX	REPORT_UPDATE_DATE
Sara	01/03/2023 00:00:00	06/03/2023 17:28:17
John	01/02/2023 00:00:00	06/03/2023 17:28:17
Peter	01/01/2023 00:00:00	06/03/2023 17:28:17

Oliver	01/03/2023 00:00:00	06/03/2023 17:28:17

I want to achieve the following:

NAMES	ADDED_ON_INDEX	REPORT_UPDATE_DATE
Sara	01/03/2023 00:00:00	06/03/2023 17:28:17
John	01/02/2023 00:00:00	06/03/2023 17:28:17
Peter	01/01/2023 00:00:00	06/03/2023 17:28:17
Matt	22/01/2023 00:00:00	07/03/2023 18:33:09
Oliver	01/03/2023 00:00:00	06/03/2023 17:28:17

I want the Report to register the date ONLY when new values date and to NOT replace current dates, so I can keep track of when the NAMES were added by the Report.

I tried the following line but it doesn't do what I want. It always replace with the time the Report ran:

| eval Report_Update = strftime(now(),"%d/%m/%Y %H:%M:%S")

And "_time" gives me the date of when it was added to the index.

Is there a specific way to register this info?

Re: Log Date when Report/Query adds new values

richgalloway — Tue, 07 Mar 2023 17:36:49 GMT

To set a field only when it's null, use the coalesce command.

| eval Report_Update = coalesce(Report_Update, strftime(now(),"%d/%m/%Y %H:%M:%S"))

Re: Log Date when Report/Query adds new values

PTC_ — Wed, 08 Mar 2023 10:34:24 GMT

Is not updating the null fields with this command.
Any idea of what I could be doing wrong?

Re: Log Date when Report/Query adds new values

richgalloway — Wed, 08 Mar 2023 13:53:53 GMT

It's possible the fields are not really null so coalesce doesn't touch them.

Can you share the full query and some sample data for it?

Re: Log Date when Report/Query adds new values

PTC_ — Wed, 08 Mar 2023 17:07:12 GMT

I did the inverse and it worked for my case:

| eval Report_Update = coalesce(strftime(now(),"%d/%m/%Y %H:%M:%S"),Report_Update)

Thank you!