Assign 'null' to date column
Hi there,
hopefully it's an easy question but I haven't managed to find the answer to it in the documentation nor the community forums.
I need to make a union of two slightly different worksheets. One is of type date and I want to set it to null so that the resulting union only has values that have meaning.
I've tried NULL, Null, null and none of them seem to be working. All I need is a column of format date with nulls/empty/blank as values for each row.
Thanks,
Lukas
-
Official comment
I believe the command you're looking for is:
ASDATE(null)The "null" is the keyword for null values and in order to cast it to a Date type object, use a function such as ASDATE which exclusively returns Date type nulls.
Comment actions -
I am using a formula =ASDATE("null") to create a null date for my column END_DT. and I am getting en error with this information:
ERROR [2017-01-31 21:34:21.212] [JobScheduler thread-1] (JobScheduler.java:829) - Job 15679 failed with exception. java.lang.RuntimeException: #L2_Mapping_ETL1!End_DT: Unable to parse the date: null at datameer.dap.common.workbook.context.WorkbookContextImpl.assertNoErrors(WorkbookContextImpl.java:715) at datameer.dap.common.job.WorkbookJob.checkForValidness(WorkbookJob.java:187) at datameer.dap.common.job.WorkbookJob.compileWorkbook(WorkbookJob.java:145) at datameer.dap.common.job.WorkbookJob.registerJobOperations(WorkbookJob.java:246) at datameer.dap.common.job.DatameerJob.createExecutionPlan(DatameerJob.java:78) at datameer.dap.common.job.DasJobCallable.call(DasJobCallable.java:116) at datameer.dap.common.job.DasJobCallable.call(DasJobCallable.java:78) at datameer.dap.conductor.job.JobSchedulerJob$2.call(JobSchedulerJob.java:116) at datameer.dap.conductor.job.JobSchedulerJob$2.call(JobSchedulerJob.java:101) at datameer.dap.common.security.DatameerSecurityService.runAsUser(DatameerSecurityService.java:109) at datameer.dap.conductor.job.JobSchedulerJob.call(JobSchedulerJob.java:101) at datameer.dap.conductor.job.JobSchedulerJob.call(JobSchedulerJob.java:41) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: datameer.dap.common.formula.SheetException: #L2_Mapping_ETL1!End_DT: Unable to parse the date: null at datameer.dap.common.formula.SheetException.create(SheetException.java:42) at datameer.dap.common.formula.FormulaSheetModel.registerColumnError(FormulaSheetModel.java:256) at datameer.dap.common.formula.FormulaSheetModel.initColumnBundles(FormulaSheetModel.java:292) at datameer.dap.common.formula.FormulaSheetModel.<init>(FormulaSheetModel.java:189) at datameer.dap.common.sheet.FormulaSheetType.createSheetModel(FormulaSheetType.java:61) at datameer.dap.common.workbook.context.WorkbookContextImpl.createSheetModel(WorkbookContextImpl.java:274) at datameer.dap.common.workbook.context.WorkbookContextImpl.registerSheetModel(WorkbookContextImpl.java:238) at datameer.dap.common.workbook.context.WorkbookContextImpl.register(WorkbookContextImpl.java:205) at datameer.dap.common.workbook.context.WorkbookContextImpl.init(WorkbookContextImpl.java:127) at datameer.dap.common.workbook.context.RootWorkbookContext.<init>(RootWorkbookContext.java:31) at datameer.dap.common.workbook.context.RootWorkbookContext.create(RootWorkbookContext.java:24) at datameer.dap.common.entity.WorkbookConfigurationImpl.createContext(WorkbookConfigurationImpl.java:594) at datameer.dap.common.entity.WorkbookConfigurationImpl.createContext(WorkbookConfigurationImpl.java:582) at datameer.dap.common.job.WorkbookJob.compileWorkbook(WorkbookJob.java:143) ... 13 more Caused by: java.lang.IllegalArgumentException: Unable to parse the date: null at datameer.das.functions.dateandtime.AsDateFunction$DetectingComputor.detectFormatAndParse(AsDateFunction.java:152) at datameer.das.functions.dateandtime.AsDateFunction$DetectingComputor.compute(AsDateFunction.java:161) at datameer.dap.common.formula.lazy.EvalSequence$ValueComputorSequence$1.computeValue(EvalSequence.java:395) at datameer.dap.common.formula.lazy.SingleEvalSequence.currentValue(SingleEvalSequence.java:31) at datameer.dap.common.formula.lazy.ArgumentsEvalSequence.currentValue(ArgumentsEvalSequence.java:141) at datameer.dap.common.formula.lazy.EvalSequence.currentIsError(EvalSequence.java:46) at datameer.dap.common.formula.lazy.EvalSequence.onlyElement(EvalSequence.java:313) at datameer.dap.common.formula.ExpressionWithArguments.simplify(ExpressionWithArguments.java:244) at datameer.dap.common.formula.FormulaSheetModel.initColumnBundles(FormulaSheetModel.java:290)
-
Thanks Eduardo! The formula appears to include quote characters around the value null. As a result, Datameer is trying to interpret this value as a STRING and not as the Java NULL value. Removing the quotes should resolve this issue.
Here's the adjusted formula: ASDATE(null)
Hope this helps!
-
Hello Brian,
I am trying to use the below formula for my requirement which says, if the value is BLANK or NULL, then make it BLANK else keep the date.
Can you please let me know, where am I using the wrong formula as the error says 'Syntax Error'.
IF(ISNULL(#Self_Validation_xlsx_Sheet3!A);ASDATE(NULL);#Self_Validation_xlsx_Sheet3!A)
Thanks
Kunal
Please sign in to leave a comment.
Comments
7 comments