0

If anyone is able to answer this quickly, I would be eternally grateful lol.

I am trying to filter all data in a CSV file for only rows with a date earlier than 6 months ago. To accomplish this so far I have created a variable called "sixMoDate" using the below steps in Pentaho. The goal was to then use this variable in a Filter Rows step.

  1. Get system info: Name- Today; Type- Today 00:00:00
  2. Calculator: Use new field constant "minus 6" of value -6, "Today" from previous step, and calculation step of "Date A + B Months" to calculate variable sixMoDate = "Today" + "minus 6". Value type of sixMoDate is Date
  3. Select Values: Change metadata of sixMoDate to ensure value type is Date. I know this shouldn't be required but as you'll see down below, this variable seems to be treated as a string instead of a date, so I put this in to see if it would solve that issue - it doesn't.
  4. Set Variable

All of the above runs correctly, even a write to log shows the variable written in the correct date format. However, when I try to "Get Variable" in a linked transformation (to later use in a Filter Rows step), I get the below error log (where "Type" is set to "Date". It only runs without error if the "Type" is set to "String", but then I get the same error on the filter rows step as I am comparing a string to a date in the filter condition.

2021/06/17 12:20:59 - Get variables.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Unexpected error
2021/06/17 12:20:59 - Get variables.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : org.pentaho.di.core.exception.KettleValueException: 
2021/06/17 12:20:59 - Get variables.0 - SixMonthDate String(13) : couldn't convert string [$(SixMonthDate)] to a date using format [yyyy/MM/dd HH:mm:ss.SSS] on offset location 0
2021/06/17 12:20:59 - Get variables.0 - $(SixMonthDate)
2021/06/17 12:20:59 - Get variables.0 - 
2021/06/17 12:20:59 - Get variables.0 -     at org.pentaho.di.core.row.value.ValueMetaBase.convertStringToDate(ValueMetaBase.java:943)
2021/06/17 12:20:59 - Get variables.0 -     at org.pentaho.di.core.row.value.ValueMetaBase.getDate(ValueMetaBase.java:2306)
2021/06/17 12:20:59 - Get variables.0 -     at org.pentaho.di.core.row.value.ValueMetaBase.convertData(ValueMetaBase.java:3862)
2021/06/17 12:20:59 - Get variables.0 -     at org.pentaho.di.trans.steps.getvariable.GetVariable.processRow(GetVariable.java:109)
2021/06/17 12:20:59 - Get variables.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2021/06/17 12:20:59 - Get variables.0 -     at java.lang.Thread.run(Thread.java:748)
2021/06/17 12:20:59 - Get variables.0 - Caused by: java.text.ParseException: $(6MonthDate)
2021/06/17 12:20:59 - Get variables.0 -     at org.pentaho.di.core.row.value.ValueMetaBase.convertStringToDate(ValueMetaBase.java:934)
2021/06/17 12:20:59 - Get variables.0 -     ... 5 more

I have tried: changing the date's format, conversion mask, date locale, timezone; getting the variable as a string and then later converting to date using metadata in a Select Values step (I get the same "couldn't convert string [$(6MonthDate)] to a date using format [yyyy/MM/dd HH:mm:ss.SSS]" error).

I've browsed every forum post about filtering based on environment variables, set/get variables, converting string to date, etc. I feel like the solution must be in the form of using a modified Javascript value step with str2date() but for some reason I couldn't get that to work (I'm a little green in Javascript). I tried this code in a Javascript step var output = str2date(SixMonthDate, "MM/dd/yyyy") and got the below error:

2021/06/17 12:43:06 - Modified JavaScript value.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Unexpected error
2021/06/17 12:43:06 - Modified JavaScript value.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : org.pentaho.di.core.exception.KettleValueException: 
2021/06/17 12:43:06 - Modified JavaScript value.0 - Javascript error: 
2021/06/17 12:43:06 - Modified JavaScript value.0 - Could not apply the given format MM/dd/yyyy on the string for Thu Dec 17 00:00:00 MST 2020 : Format.parseObject(String) failed (script#2)
2021/06/17 12:43:06 - Modified JavaScript value.0 - 
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.addValues(ScriptValuesMod.java:476)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.processRow(ScriptValuesMod.java:548)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at java.lang.Thread.run(Thread.java:748)
2021/06/17 12:43:06 - Modified JavaScript value.0 - Caused by: org.mozilla.javascript.EvaluatorException: Could not apply the given format MM/dd/yyyy on the string for Thu Dec 17 00:00:00 MST 2020 : Format.parseObject(String) failed (script#2)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.DefaultErrorReporter.runtimeError(DefaultErrorReporter.java:109)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.Context.reportRuntimeError(Context.java:945)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.Context.reportRuntimeError(Context.java:1001)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesAddedFunctions.str2date(ScriptValuesAddedFunctions.java:1049)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at java.lang.reflect.Method.invoke(Method.java:498)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:161)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.FunctionObject.call(FunctionObject.java:413)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.optimizer.OptRuntime.callName(OptRuntime.java:97)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.gen.script_5._c_script_0(script:2)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.gen.script_5.call(script)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:426)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3178)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.gen.script_5.call(script)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.mozilla.javascript.gen.script_5.exec(script)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.addValues(ScriptValuesMod.java:388)
2021/06/17 12:43:06 - Modified JavaScript value.0 -     ... 3 more

NOTE: I've tried ensuring the specified date format is consistent with what the source "String" is using as suggested by this thread, to no avail: How to convert string to date type in the Pentaho?

PLEASE help!!!

Jote Muti
  • 1
  • 1

1 Answers1

0

Variables are only of string type, so it's in the linked transformation reading the variable where you have to convert that variable to date. The transformation with the Set variable step has to receive a string value with the format you are expecting in the linked transformation to reverse the conversion from string to date again.

Ana GH
  • 1,397
  • 1
  • 9
  • 19