topic How do I replace null value in Pivot? in Reporting

How do I replace null value in Pivot?

juzzbitzz — Fri, 28 Dec 2018 09:40:29 GMT

I have a pivot table that has a null value for a few of the records in the "Date" column that is preventing me from sorting the table. How and with what do I replace the null value in the Date column?

Re: How do I replace null value in Pivot?

renjith_nair — Sat, 29 Dec 2018 11:56:47 GMT

@juzzbitzz,

Try adding |fillnull value="your value for date" Date in the base search before aggregation/sort

Re: How do I replace null value in Pivot?

DalJeanis — Mon, 31 Dec 2018 18:46:21 GMT

You need to populate the Date field.

Identify, on the records with no Date, what is a good candidate for substitution, then do something like

| eval Date = coalesce(Date,mySubstituteField)

Re: How do I replace null value in Pivot?

woodcock — Tue, 01 Jan 2019 04:28:25 GMT

You have 3 options:

...  | eval Date = coalesce(Date, yourAlternateFieldHere, "Your Hard-Coded Default Value Here")

...  | fillnull value="Your Hard-Coded Default Value Here" Date

...  | filldown Date

I leave it as an exercise for the reader to figure our (or @DalJeanis to explain) the differences.