Help with creating a new column calculated from several other columns

[Anonymous]

Hi all

This is a complicated one and I am looking for some creative solutions to this. I've tried various methods but I think my knowledge of Talend may be letting me down here.

I'll begin with that I am trying to achieve then explain. I want to insert a new column which shows one of the following values - ANSWERED, UNANSWERED, BLOCKED, UNAVAILABLE, ABANDONED based on looking at other columns with various combinations. However, it is not as straight forward as that. Part of the problem I am having as well is two of the columns I need to look at are in date format.

There are 4 columns with values which combined create the value for the new column which we shall call TYPE for the purpose of this. The columns are ChatType, ChatStatusType, Answered, Started. ChatType & ChatStatusType contain numbers and are listed in Talend as strings (that can be changed if it will resolve this issue) Answered and Started are date fields (that cannot be changed unless a duplicate of the column is created as I need to preserve the original date columns).

Below is a key of how the TYPE values are calculated:

ANSWERED       Anything with an Answered time
UNANSWERED   Anything with a started time but no Answered time
BLOCKED         ChatType 5 and any ChatStatusType
                      ChatType 8 and ChatStatusTypes 10,13 or 18
UNAVAILABLE ChatType 3 and any ChatStatusType
                    ChatType 8 and ChatStatusTypes 7,8,11,12,14,15,16 or 17
                    ChatType 0 and ChatStatusTypes 7,8,11,12,14,15,16 or 17
ABANDONED     Anything not already catagorised by the above


Important things to note, originally I have hoped I could work this all out without the use of the date columns. I looked at concatenating the ChatType, ChatStatusType and using a look up however, it is possible to have a combination in ChatType, ChatStatusType that on it's own may read as one type but is actually another once you factor in the date columns. If the date columns can be duplicated and then replaced with 1 for contains a value and 0 for does not in the tMap then in theory a concatenation of all 4 columns could be used for a look up or replace after the tMap. However, I have yet to be able to duplicate the column in the tMap and convert it's value to a 1 or a 0 and join it with the other columns needed to create a final column with a numerical value which could be looked up.

Attached a screenshot showing sample data. The red column I have put in using excel manually to show what the output of the new column should look like.

This seems like a complicated nightmare to me but there might be a very simple way to resolve it. Any thoughts welcome, I assume some kind of lookup or replacement after the tMap is going to be the easiest way to do this but I'm struggling to output two duplicate versions of the date columns from the tMap containing 1's and 0's and have them be added to a new column with the other columns required.

[Anonymous]

There are different ways to solve this.
The most simplest one is to build the status values within a tMap in the variable section or a tJavaRow component with a small portion of Java code.
The other way is (if your target is an Excel sheet - it looks like) to setup a excel sheet with a data validation in your status column and define the rules here in excel. You can write into such a sheet with the component tFileExcelSheetOutput with the activated Data Validation option and Excel will fill this column for you while the file is opening in Excel.

[Anonymous]

Hi thank you very much for replaying. This will be exporting into an SQL database as well as outputting to an excel sheet so I would ideally like to keep the solution good for both.

I've had a look into what you have suggested but as I am so new to this it's a little bit lost on me as I am not sure where to start. Is there any way you could give a bit more detail on the methods involving the variables and the tJavarow?

Thanks again