Assign 'null' to date column

4 followers
0
Avatar

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

Lukas Kinduris

Official comment

  • Avatar
    Brian Junio

    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.

    0

5 comments

  • Avatar
    Eduardo Martinez

    I am getting this error, when I use that function:

    Caused by: java.lang.IllegalArgumentException: Unable to parse the date: null

     

     

    0
  • Avatar
    Joel Stewart

    Eduardo, could you please share the rest of the stacktrace? It's difficult to understand the situation just from the single error message.

     

    0
  • Avatar
    Eduardo Martinez

    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)

     

    0
  • Avatar
    Joel Stewart

    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! 

    0
Please sign in to leave a comment.