LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Buddha

I'm having trouble converting my dates into actual date format.
I need the date format to be 2015-08-01 (or 2015/08/01)
my original data had the date as 01-Aug-15
using regexSubstituteI I changed the months into numbers. leaving me with 01-08-15
now I need to reverse their order.
I would normally do this in excel with a Right Mid Left set of commands, but I don't know of a Mid function in LS.
look forward to hearing ways to do this
thanks

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

You can use
date(myDate,"DD-m-YY")

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Buddha

Thanks Stonysmith, I tried that with no luck (tried all sorts of combinations using the above script).
I continue to receive the error "Cannot parse value with format "DD-m-YY"

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Is that the whole error message? It usually shows you the value that can't be converted.
Is the input column already a date datatype? It needs to be a string.
Are there multiple formats within the column?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Buddha

sorry the error is actually cannot parse value " with format 'DD-m-YY'
the format of the column is in string format but there are nulls within it, could that be causing the issue?

I have removed nulls and blanks and the filter runs fine now with the script
newdate = date('OLD_PROM_END_DT',"DD-m-YY")
emit *, newdate

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Right. One small thing.. an empty string '' works differently than NULL. The date() function will work with a null, but not an empty string.

You could do this:


if isDate('OLD_PROM_END_DT',"DD-m-YY") then newdate = date(newdate,"DD-m-YY") else newdate = date(null)
emit *, newdate

The advantage to this method is that you could handle several different formats at once:

newdate = date(null)
if isDate('OLD_PROM_END_DT',"DD-m-YY") then newdate = date('OLD_PROM_END_DT',"DD-m-YY")
else if isDate('OLD_PROM_END_DT',"DD-MM-YY") then newdate = date('OLD_PROM_END_DT',"DD-m-YY")
else if isDate('OLD_PROM_END_DT',"MM-DD-CCYY") then newdate = date('OLD_PROM_END_DT',"DD-m-YY")
.......
emit *, newdate

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Buddha

that's awesome info, thanks for that... that's going into the keep file

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Buddha

I was having a few issues with your script so I tweaked it (I think you had made a small error referencing the variable instead of the column) but now it works beautifully

if isDate('OLD_PROM_END_DT',"DD-m-YY") then newdate = date('OLD_PROM_END_DT',"DD-m-YY") else newdate = date(null)

thanks again

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gabygyy

HI,

i'm using a metadata change node and i'm having some problems with date fields.
The source file is an xls file read as unicode by the xls node and the final scope is to store data into a database table.
After reading file i'm using a metadata change node for converting all columns to string.
From string i must adapt them to data type expected by orcale table, and in this case i'm using another metadata change with 2 inputs:
-1 all columns as string type
-2 all columns names and their data type as expected by oracle( in this case i have for eg more DATE fields in input as string Effective Time
15/05/2000
21/04/2015
04/01/2000
05/05/2015
01/09/2014
Note that in input are many DATE columns with null values also and with other formats also.
I fon the last metadata chnage node i don't specify the date format string the node is crashing
"
2015-09-15 11:37:54.019; ERROR: Error threshold breached! Too many errors occured processing data
id: 5 chain: 0 group: 0
javaDetail: com.lavastorm.brain.node.lal.changeMetadata.Change Metadata.outputUnconverted(ChangeMetadata.java@600 )
dataDetail:
metadata changes(in) row: 4
columns: ['NewNameField', 'java.lang.String'], ['NewTypeField', 'java.lang.String'], ['OldNameField', 'java.lang.String']
records:
[Null, Null, Null]
errors(out) row: 1
columns: ['EFFECTIVE TIME', 'java.lang.String'], ['Expiration Time', 'java.lang.String'], ['Subscription Effective Time', 'java.lang.String'], ['Subscription Expiration Time', 'java.lang.String'], ['_Error', 'java.lang.String'], ['_ErrorCode', 'java.lang.String']
records:
[01/01/2000, 01/01/2037, 01/07/2014, 01/01/2037, Field 'EFFECTIVE TIME', Record#1 - Could not convert EFFECTIVE TIME to date; java.text.ParseException: Unparseable date: "01/01/2000", ls.brain.node.changeMetadata.conversionError]
changed data(out) row: 0
columns: ['EFFECTIVETIME', 'com.lavastorm.lang.Date'], ['EXPIRATIONTIME', 'com.lavastorm.lang.Date'], ['SUBSCRIPTIONEFFECTIVETIME', 'com.lavastorm.lang.Date'], ['SUBSCRIPTIONEXPIRATIONTIME', 'com.lavastorm.lang.Date']
input data(in) row: 1
columns: ['EFFECTIVE TIME', 'java.lang.String'], ['Expiration Time', 'java.lang.String'], ['Subscription Effective Time', 'java.lang.String'], ['Subscription Expiration Time', 'java.lang.String']
records:
[01/01/2000, 01/01/2037, 01/07/2014, 01/01/2037]
"

If i specify the date format as dd/mm/yyyy then is running but the output data is wrong like this
in the input's oreder
2000-01-15
2015-01-21
2015-01-04
2015-01-05
2014-01-01
Please advice what can i do to solve the issue to treat as much cases posible without hardcoding something for each column!

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gabygyy

any ideea what are the min and max dates that can be read by BRE nodes?
for example 2099-12-31 is read as 31-12-1843

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

How're you reading in the dates? I can set a date year of 1 and also 9999.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gabygyy

the format at input is dd/mm/yyyy
Just now i've put some dummy values in an static node and after this link with change metadata with default settings(no format specified for date fields)
input
Datefabc:date
2096-10-08
2097-10-08
2098-10-08
2099-10-08
2100-10-08
output from change metadata
Datefabc
2096-10-08
2097-10-08
1842-10-08
1843-10-08
1844-10-08

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Strange... Definitely looks like a Change Metadata issue. I'll go ahead and report this. If you're in a bind and need a quick work-around, convert the dates to a string before the Change Metadata node and then convert it after the Change Metadata.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gabygyy

yes, this is the workaround that i also found in order to be able to store the data-but this is a manual action that must be explicitly made.
thx, i will wait for a fix if it will be soon. Also check also with a lower date like 1943-12-31 there is also a lower limit issue for this also.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ritban

Hello,
I have a similar date problem where the year 2099 is not converted properly. I am using lavastorm version 5 and don't see any Change Metadata node. Is this node specific to any version?

Regards
Ritban

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: simon.wilby

Hi,

first off, the min and max date issue has now been resolved in release 6.0.41 and 6.1.41 of LAL; this is available to customers on LAE 6.0 and 6.1 respectively.
The change metadata node was released pre LAE 5.0 so if you're using LAE 5.x then you should find the node in the Metadata and Structure category. If it is not there then it's worth just checking that you have lal1 as a base library in bre.

hope that helps

Simon

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awilliams1024

Hi gabygyy,

To expand Simon's comment:

The latest release of LAL for your version of Lavastorm can be downloaded from the Lavastorm website: http://www.lavastorm.com/lavastorm-installers/

Note, the above link redirects you to the downloads page that is currently hosted on Salesforce here:
http://na3.salesforce.com/_ui/selfse...0150000000s09A

Best regards,
Adrian

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ritban

Thanks Simon.. I'll check the base library

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ritban

2099_date_issue.brg2099_date_issue.brpMetadata_error_log.zip2099_lavastorm_sample.zip

Hello ,
I'm still getting errors when I try to change data type for year 2099 and 2098. The graph I'm trying to use ,error log, source file is attached for your reference. The error log says Unparseable data and I'm confused is this a source data format issue as these files come from another application .

Regards
Ritban

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ritban

Hello Simon,
I wish to convert input string type column to date type output column through the Change Metadata node. The graph I'm trying to use is attached for your reference.I'm getting an error of unparseable data. How do I convert this into a date output. Please help.

Regards
Ritban

2099_date_issue.brpMetadata_error_log.zip

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

The output data is not "incorrect". The data has been converted into an internal date-time representation. It's displaying in the BRD Viewer as 2015-01-21, but it's properly a date internally. You can use dateAdjust() and dateSubtract() as well as sort the data and you'll get the right values.

You just have to do a bit of work to get the dates into a different format when exporting them.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ritban

I want this output to exported into excel and hence want to know where and how to set the dateformat string parameter. The input string to Change metadata node is "DD/MM/YYYY" formt

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ritban

Hello All,
Is there any solution to this issue. I'm still stuck

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Your zip file contained only the error message (not the graph), but that was enough...

On ChangeMetadata, go to the Optional Tab and for the DateFormatString parameter, specify "dd/MM/yyyy" without the quotes

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ritban

2099.zip2099_lavastorm_sample.zip

Hello Stonysmith,
Thanks for your help.The output is coming out differently though. I am using values like 25/12/2098 in source csv file and post metadata change the new value comes as 2097-12-25. Why are the values not changing properly? The graph, output generated and input file is attached for your reference.

Regards
Ritban

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

In this case, the capitalization matters significantly. Use lower case dd, uppercase MM, and lowercase yyyy

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ritban

Stonysmith.. Big Thanks..it works

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ritban

2099_date_issue_notworking.zip

Hello Stonysmith,
I'm still getting error after using Change Metadata node on field Cut Date whenever a new file comes with these dates.I get these files in a csv format.The output excel is not written properly though internally it's showing correct dates.The graph,input and output excel are attached.

Any pointers to fix this once and for all would be appreciated.

Regards
Ritban