Assign 'null' to date column

6 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
    Comment actions Permalink

7 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
    Comment actions Permalink
  • 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
    Comment actions Permalink
  • 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
    Comment actions Permalink
  • 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
    Comment actions Permalink
  • Avatar
    Kunal Gaurav

    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

    0
    Comment actions Permalink
  • Avatar
    Konsta Danyliuk

    Hello Kunal.
    Please try to use ASDATE(null) instead of ASDATE(NULL). I've tested at my Datameer-7.4.3 lab and ASDATE(null) works fine, while ASDATE(NULL) returns syntax error.

    IF(ISNULL(#Self_Validation_xlsx_Sheet3!A);ASDATE(null);#Self_Validation_xlsx_Sheet3!A)
    0
    Comment actions Permalink
Please sign in to leave a comment.