0

OK, so a programming-challenged analyst has data like this:

ID          NAME    DATE
SYSID-1054  XYZ     1
SYSID-1054  XYZ     2
SYSID-1054  OPQ     3
SYSID-1054  OPQ     4
SYSID-1054  XYZ     5
SYSID-1054  XYZ     6
SYSID-1054  XYZ     7
SYSID-1055  ABC     8
SYSID-1055  ABC     9
SYSID-1055  DEF     10
SYSID-1055  DEF     11

The goal is finding records where NAME changes from day to day for the same ID, output should be this:

ID          NAME    DATE
SYSID-1054  XYZ     1
SYSID-1054  OPQ     3
SYSID-1054  XYZ     5
SYSID-1055  ABC     8
SYSID-1055  DEF     10

Yes, I know that in SQL/some programming language this is simple to do. I'm looking for a GUI-based tool for the analyst who's unable/is not willing to learn programming who still wants to construct data processing pipeline in the app that can compute results like this.

So far I've looked at Orange, but have been unable to get result like this in the app (perhaps it's because I know little how to use it for now), but am also looking for alternatives. Can KNIME do this? Some other tool? Please recommend some tool that can transform data like this without writing code or with writing little code.

1

I suggest you to use OpenRefine.

You first need to add a new column based on the ID and NAME columns, e.g. using this expression: cells["ID"].value + ";" + cells["NAME"].value

Sorting by the new column, using the "Blank down" feature and then creating a "Facet by blank" allows you to remove the duplicate ID+NAME combinations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.