Automatic LogID Using Date-Time Combinations

[SNasif]

Hello Community,

I know this is probably a rather simple task to achieve but for some reason I seem to be running into issues applying it. I have a series of 35 to 40 jobs/subjobs that run on a schedule. Each job or subjob has a tstatcatcher component that collects a series of information. The output is stored into an Oracle DB table. The jobs were built by someone before they were handed off to me and while looking at the collected logs, I noticed that the rows inserted previously would have a log id relative to the date-time combo of when it was triggered, i.e.: if I triggered the jobs on the 1st of june 2023 at 8pm the log ID for all jobs/subjobs on the collected output would show 20230601200000 (24hour time format with minute and seconds). For some reason it was reverted to a fixed value of 1. Now I'm trying to replicate that logic again by using the following expression on a tmap component before using the output for the table input: talenddate.formatdate("yyyyMMddHHmmss", talendate.getcurrentdate()).

Firstly, I'm unsure whether that is the right expression to use. Secondly, whether I map the output to the DB table, a fresh excel sheet or even a delimited output, I constantly get an error mentioning "illegal pattern character f".

Any help or guidance on how to deal with this issue would be appreciated. Noting that I would like to be able to use a log id based on date time of trigger globally for all jobs and subjobs getting tiggered.

Thank you!

[HKirk1]

Hello,

The error you're encountering with the "illegal pattern character f" indicates that the pattern you're using in the talenddate.formatdate() function is not recognized. The pattern character "f" is not a valid character in the context of the SimpleDateFormat class used by Talend.

To achieve the desired date-time format (yyyyMMddHHmmss), you can modify your expression as follows:

TalendDate.formatDate("yyyyMMddHHmmss", TalendDate.getCurrentDate())

Make sure to use uppercase "T" in TalendDate and TalendDate.getCurrentDate().

By using this expression, you should be able to generate a log ID based on the current date and time in the specified format (yyyyMMddHHmmss).

Please note that this solution assumes you are using Talend Data Integration or a similar product that provides the TalendDate functions. If you are using a different tool or version, the syntax or functions might vary. Pay by Plate Ma

Hope this helps!

Best regard,

P Pride

[SNasif]

Hello,

Firstly, thank you for taking the time to provide an answer. Secondly, I'm not facing any issues due to formatting or case sensitivity. I made sure that the expression is written and formatted properly, will attempt to redo it and see what results it could generate. I am currently on Talend Data Integration on version 8.0.1 so the TalendDate expressions are available to use. Should you have any other suggestions it would be greatly appreciated as I've already tried different date and date time formats as well but nothing seems to work (referred back to the Talend documentation for valid date and date time formats).

Thank you for your time.

[Lord-Vader · Accepted solution]

Ok. Im not sure what your trying to do but ill lay some info here.

This idea = talenddate.formatdate("yyyyMMddHHmmss", talendate.getcurrentdate()).

returns a string and not a date and its wrong.

So if you want to insert a DATE in the column.

1. Check what type your DB_COLUMN has and what your output is.
2. use this in the tmap variable : TalendDate.getCurrentDate() (this returns a date)
3. in the column put your format for output :
4. then you will get this : 2023-06-05 08:53:24
5. 
6. then you will get this : 20230605090925

You should always check what your outputs, inputs, column types are and always read the functions returns in the tmap expression gui.

Again not sure what your trying to achieve.

I hope this helps you.

Vader out 💪.

[SNasif]

Hey Vader,

I absolutely agree that I may have missed some context out. I report on a daily basis what jobs have passed/failed based on querying the log that's collected from the tStatCatcher as well as other db queries. ultimately, I would like to collect the log id based on the date time combo in order to isolate start/end pairs in the log and report durations spent.

The issue on my end is I may end up running the same job/subjobs multiple times a day. If I were to run it at 9am this morning, it would mark all the jobs with a 0900 on the log, then if i were to run it again at 2pm lets say, it would mark it with a 1400, just to make it easier on myself to filter the runs out.

Ignoring the entire DB column issue aside, even if I were to map the output to a tlogrow or delimited output, it is still generating the same error "illegal pattern characted f". However, I see your point concerning the mapping and format output and I assume I might be missing something there, will adjust accordingly and get back to you.

I made sure to thoroughly check my work and refer back to the documentation as well as other posts before posting myself. I'm mostly perplexed that I still get the same error despite formatting as date only on a separate test run and still generating the same error (data types also adjusted accordingly obviously.)

In any case, thank you for taking the time to respond and I will take your suggestions into consideration.

[Lord-Vader]

Hey @Sam Nsaif​ .

If its possible can you share your date format that you are using ? Then i can try to recreate your issue.

Vader out 💪.

[SNasif]

Hey Vader,

I seem to have figured it out, guess I just messed up the formatting and gave the output multiple formats between the tmap output and the expression itself.

Last question, is there any way to capture the first generated log id and sort of use that as the global log id for the entire run?

Thank you for your help!

[Lord-Vader]

Hey SNasif,

Yes you can depending on how you build your job and where your getting the logID, you can use different components to get it.

i would use the tfixedflowinput component and limit it to 1 row and you will get the first id basically or use the taggregaterow and set the function of the columns to first and you will get the first row and add it to a variable, but dont forget everytime you iterate/load new data on these component it will output a new id of the first row.

To combat this maybe you will have to write code or maybe put a fixed value(1) in a variable and match it with the row.

Hope this helps.

Vader out 💪.