DQL nice to know: Using a filter, but when not in filter then show record with empty attribute

[henk_stobbe]

Hello,

Maybe this is handy for someone. Suppose you want to use a filter, but you also want to show records not within the filter with an empty attribute 

You need to fetch the data twice (-; The "data" example:

      data record(rec=1,data =1),
      record(rec=2,data =2) ,
      record(rec=3,data =1),
      record(rec=4,data =3)
| filter (data == 1)
| fieldsadd newdata = data
| append [
                  data record(rec=1,data =1),
                           record(rec=2,data =2) ,
                           record(rec=3,data =1),
                           record(rec=4,data =3)]
| dedup rec, sort:(newdata)                                         // Use the "null" values last!
| fieldsAdd data = if(isNull(newdata), "", else:data)  //  Replace ugly null by ""
|fieldsRemove newdata                                              //   Remove the temp column

Result:

Any comments, issue's or has a better way?

KR Henk

[Tiit_Hallas · Accepted solution]

I might be missing something here, but why not just use the IF clause without the append?

data 
  record(rec=1,data=1),
  record(rec=2,data=2),
  record(rec=3,data=1),
  record(rec=4,data=3)
| fieldsAdd data = if(data == 1, data, else: "")

An example of the result can be seen in the Security Investigator in Dynatrace Playground: https://wkf10640.apps.dynatrace.com/ui/apps/dynatrace.security.investigator/share/2e02f2c7-8781-4e95...

[henk_stobbe]

Hello,

Thanks for your reply!

You are not missing anything, from where I am sitting you are brilliant!

KR Henk