Hi Sabrina, I have situation regarding Milliseconds to Date Conversion. There are 2 columns in excell sheet which contains only millisecond values and that have to convert into a Given Date format Please take following table as Source table.. TRISTARTDATEDA TRIENDDATEDA 1425148200 1423074600 1423074600 1482604200 Expected Outcome... TRISTARTDATEDA TRIENDDATEDA 01-03-2015 05-02-2015 05-02-2015 25-12-2016
Please kindly suggest me solution for this . I am Waiting for positive reply
Hey, Thanx for your reply but it doesnt seems to be working it giving type casting error Type Mismatch : Cannot convert from Long to Date Type Mismatch : Cannot convert from Date to Long My Source values are coming with Long Type and i put that logic you mentioned above in Target column and i give Date type to it Is it Right?
Hey, Thanx for your reply but it doesnt seems to be working it giving type casting error Type Mismatch : Cannot convert from Long to Date Type Mismatch : Cannot convert from Date to Long My Source values are coming with Long Type and i put that logic you mentioned above in Target column and i give Date type to it Is it Right? Kindly find the uploaded image of mytMap.... If i put long type in the target also then job runs but it gives wrong values like 17-01-1970
Hi, Please try to use below expression in tMap: new java.util.Date(row1.unixtimestamp) Best regards Sabrina
Hello Sabrina, Thanks for your prompt reply, I tried using your logic it seems to be working for ex-1298226724 but when i want to convert unixtimestamp 1443696342801 into date with timestamp i am getting error :invalid error please check below error Starting job unix at 22:38 09/01/2016. connecting to socket on port 3771 connected Exception in component tOracleOutput_1 java.lang.IllegalArgumentException: Invalid year value at oracle.sql.TIMESTAMP.getOracleYear(TIMESTAMP.java:786) at oracle.sql.DATE.toBytes(DATE.java:634) at oracle.sql.DATE.toBytes(DATE.java:521) at oracle.sql.DATE.<init>(DATE.java:116) at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9325) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8954) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9548) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:249) at local_project.unix_0_1.unix.tOracleInput_1Process(unix.java:799) at local_project.unix_0_1.unix.runJobInTOS(unix.java:1236) at local_project.unix_0_1.unix.main(unix.java:1093) disconnected Job unix ended at 22:38 09/01/2016. for your reference i am attaching the unix epoch converter output 10/1/2015, 1:45:42 PM i want the output as 10/1/2015, 13:45:42 in 24hrs clock
Hi Sabrina & All, Kindly attention Please .... I have situation in one of ETL job. i want to convert or remove special charterers from data Please find Image attached, it has some special charecters and these may found in some of the data , so how could we handle this by ETL. There is one column Replace_By which has values that have to replaced by there respective Special Characters. and that particular row which has that Special Character mentioned there have to stored in other file/table/tlogrow. Please revert if you have any query regarding this. .
Hi Sabrina Thans for your suggestion , I have implemented it and its working fine I am facing another problem now i.e. I want to apply the particular kind of logic on multiple columns present in source I am have already created a job like this What I want is to repeat the process for multiple columns as follows can you please guide me regarding this.... waiting for reply
Hi All, 1 general Question, What we can do at data warehouse side when we need to delete bunch of records.? or for which things we should take care about before start delete records from data warehouse? Except Backup, is one of them. - What are Best Practice ? - Operations on DWH before start delete?
Hi Everybody, I need help from you, I am trying to implement an ETL job. I have REMARK column in target table which holds remarks for every column if that particular column failed at validation. Ex - Suppose that I Have ID column in source (Excel), it should hold only Numeric values but in case if it contains any character then that particular record should go under error table and Remark column hold remark that"ID should be Numeric." Like wise, i need to implement it for multiple columns & REMARK column should be updated for every column if they all got failed at validation. like Date Fields- Start_Date Should be less than End_Date. Description Field - Should contain only 1000 characters max to max. Any Required (*) field should not be null. Boolean fields contain only TRUE & FALSE in capital form, should not contain any 'Yes', 'No',123 or nul
I have done it after all, Please see image attached here and solution for it as follows- 1)Sort out data on the basis of EXPECTED & REJECTED records through your business rule like NAME should not be null if it is null then it should go under ERROR table. After that take 1 variable and put this logic - Source.triNameTX==null?"(triNameTX Should not be Null)":"" and use this variable in REMARK column of ERROR table. 2)START date should less that END date - Var2- (Source.triSTARTDA != null && (Source.triSTARTDA.before(Source.triENDDA))) || Source.triSTARTDA == null (Boolean Type) Var3 -(Source.triSTARTDA!= null && (Source.triSTARTDA.after(Source.triENDDA)))||Source.triSTARTDA == null?"(triSTARTDA Should'nt be NUll or Less than triENDDA)":"" 3) Now, Concatenate Var.var1+Var.var2 and run your job & after completion of your job take look into REMARK column , you will see -(triNameTX Should not be Null)(triSTARTDA Should'nt be NUll or Less than triENDDA). so like wise you can do it for many columns as per your Business Rule.