Tester filter not working

A feature can never be inserted and unchanged...

is there any way

Yes, when I use "OR," it works. But I need to push unchanged data to the database if there is a value "inserted".

anyone

FME tests individual features. When you use AND in the Tester, no features will pass, as an individual feature cannot have the value fme_db_operation = INSERT and fme_db_operation = UNCHANGED at the same time. When you use OR in the tester, some individual features wil pass because fme_db_operation = INSERT, other individual features will pass because fme_operation = UNCHANGED; features with other values will fail the test.

When insert and unchnaged values, I need both values to be inserted. If only a value is unchanged, it should not be inserted. how can I achieve this

Can you provide us with some example data of all relevant categories, with a short description of the data that should pass, and the data that should not pass, and why this is the case?

There is Three Columns

One is Basin (“AL”,”EP”) 2nd Column is Stormnum (“1”,”2”) and time (“2023/07/25 9:00:00z)

Storm num and time change everything when they release the new data

There are two areas and two files.

extracting the data from two files and If they release the data for "AL" at 2:57, my script will insert this (Consider as new). Then, at 3:00, the "EP" release will occur, and my script will run again. It will truncate the "AL" file and insert the new record for "EP".

 and we don't need any historical data when new data arrival just truncate the old one and insert the new one

you can change the time in validtime or stormnum for your testing

if database doest work please yo can use excel to compare

Can you provide the Excel file please?

I want to check if I understand correctly. Is this the data flow you are looking for?

An AL feature enters. Add it to the database.

An EP feature enters. Add it to the database, and remove all AL features that have the same values in ADVNUM, BASIN and VALIDTIME as the EP feature.

NO

Now you can see there are 2 records in AL and 1 Records in EP right

1st Scenario

now if EP changes the storm number then in the Excel file only change the EP and AL stays there.

2nd Scenario

if both change the storm number then truncate the data and insert the new one.

New try. The last one for today, as I am at the end of my working day.

Is this data flow correct?

An AL feature enters. Add it to the database.

An EP feature enters. If it's storm number already exists in the database, update the EP feature in the database that has the same storm number with the data from the new EP feature (don't touch the other features). But if it's storm number does not exist in the database, remove all data, and insert the new EP feature.

If it is not correct, can you describe the different data flows?

Any basin feature enters. Add it to the database.

An EP/AL feature enters. If its storm number already exists in the database, update the Any basin feature in the database that has the same storm number with the data from the new

 but maybe EP has a new record and Al will not so we don't know which basin update first and which one is not.

For Example at the moment we have this is excel file

Basin StormNumber validTime

AL. 3 2023/07/25 9:00:01z

AL 4 2023/07/25 9:00:00Z

EP. 3 2023/07/25 9:00:01z

NOW NEW data arrive at 25/07/2023 10:00:00z

Basin StormNumber validTime

AL. 3 2023/07/25 10:00:00z

AL 4 2023/07/25 9:00:00Z

EP. 3 2023/07/25 9:00:01z

so my script will work and truncate the all data and insert only this one

AL. 3 2023/07/25 10:00:00z

logically I need

AL 4 2023/07/25 9:00:00Z

EP. 3 2023/07/25 9:00:01z

this one as well in data as source have this records as well but in my data inserting only new one

again if data release the data at 25/07/2023 10:30:00z

AL. 3 2023/07/25 10:00:00z

AL 4 2023/07/25 9:00:00Z

EP. 3 2023/07/25 10:30:01z

so I need all records in database

I think you get my point

In the example you give above, you can always truncate the existing data, and then write the features from the Inserted port of the ChangeDetector (with Match Selected Attributes set to BASIN STORMID VALIDTIME).

If I understand correctly, you are writing to Excel, so you can simply write the inserted features, overwriting the existing Excel file.

no i m not writing on excel I m writing on snowflake database

what if source have new data with old data

target will truncate the old one and insert the new one and what about the unchanged one

so bascly I want when insert has value and unchanged also then insert the data if unchanged only then script stop there

Sorry, I still don't understand the logic of the different data flows. The information is shattered across too many posts in this topic. Can you please make a summary?

I hope it is as simple as this:

When a feature enters with BASIN = AL, and STORMNUM is already present in the database, do this: ...

When a feature enters with BASIN = EP, and STORMNUM is already present in the database, do this: ...

When a feature enters with BASIN = AL, and STORMNUM is not present in the database, do this: ...

When a feature enters with BASIN = EP, and STORMNUM is not present in the database, do this: ...

But maybe it is more complicated. In your examples 2 AL features and 1 EP feature enter at the same time. So possibly there are dependencies between the features?

When a feature enters with BASIN = AL, and STORMNUM is already present in the database, Do Nothing (Stay unchanged)

When a feature enters with BASIN = EP, and STORMNUM is already present in the database, Do Nothing (Stay unchanged)

When a feature enters with BASIN = AL, and STORMNUM is not present in the database, Insert it as New But truncate the exiting one

When a feature enters with BASIN = EP, and STORMNUM is not in the database, Insert it as New But truncate the existing one

When a feature enters with BASIN = AL or EP and STORMNUM is not in the database, Insert it as New But truncate the existing one but if EP or AL and STORMNUM are already present in the database Do Nothing (Stay In database)

So If New data arrive in AL or EP insert with unchanged

Unfortunately I'm even more confused now...

Earlier you wrote:

"For Example at the moment we have this is excel file

Basin StormNumber validTime

AL. 3 2023/07/25 9:00:01z

AL 4 2023/07/25 9:00:00Z

EP. 3 2023/07/25 9:00:01z

NOW NEW data arrive at 25/07/2023 10:00:00z

Basin StormNumber validTime

AL. 3 2023/07/25 10:00:00z

AL 4 2023/07/25 9:00:00Z

EP. 3 2023/07/25 9:00:01z

so my script will work and truncate the all data and insert only this one

AL. 3 2023/07/25 10:00:00z"

I interpreted this as: When a feature enters with BASIN = AL, and STORMNUM is already present in the database, if VALIDTIME is more recent, remove all existing features from the database and insert this feature.

But now you say: "When a feature enters with BASIN = AL, and STORMNUM is already present in the database, Do Nothing (Stay unchanged)", which seems to be inconsistent with what you wrote before?

When a feature enters with BASIN = AL or EP and STORMNUM is not in the database, Insert it as New But truncate the existing one but if EP or AL and STORMNUM are already present in the database Do Nothing (Stay In database)

At one place you say that the record in the database should be updated when STORMNUM is already in the database, at another place you say to do nothing. It's not possible to do both at the same time.

Truncate is to remove all records from the database table. But it seems that sometimes it is wanted to remove only all records of one basin?

There are two scenarios I want :

1. When both insert and unchanged values are present.

2. When only insert values are present.

I need the change detector to insert and unchanged values when both are present and when only insert values when there are no unchanged values. Otherwise, it should do nothing.

either remove only the records of one basin or remove all records