How to evaluate a blank field in a Dimension?

[Coolavin]

I have a measure that uses the expression below:

=COUNT( {$-<[CredentialExpireDate]={'*'}>}[CredentialMeasure])

This expression says: if "CredentialExpireDate" is not anything than count "CredentialMeasure"

I need the same type of expression in a dimension. It needs to look at "CredentialExpireDate" and if it's not anything then place the text "Active" in the a field otherwise it should place "Inactive" in the field. 

I have tried checking if "CredentialExpireDate" is null

=if(not WildMatch([CredentialExpireDate],'*'),'Active', 'Inactive')

=if(isnull([CredentialAchievedDate]),'Active', 'Inactive')

=If (Len(Trim([CredentialAchievedDate]) < 0),'Active', 'Inactive')

=If(Len([CredentialAchievedDate]) > 0,'Active', 'Inactive')

None of the above work. The blank fields in "CredentialExpireDate" are empty. They don't have a null value or anything at all. Thats why I need a way to check if it's not anything like with the first expression that works for the measure. I can't find a way to do this in a dimension. 

[robert99]

Hi @Coolavin 

"I don't know what you mean by using SCRIPT."

Until you do you won' t get far using Qlik. You can do some fairly basic thing using the automatic script generator (Data Manager) but the more complex stuff needs scripting. Or you can add a new field by going into Data Manager and doing it there. But both preferable needs a little understanding regarding scripting.

And you shouldn't be trying to do what you are doing using a measure. You must use a dimension. There are two ways to set up a dimension that you can use to drill down

- Set it up as part of the loading data process. Using either 'data manager' or 'Data Load Editor'. This is the best way to do this by far.

-Use aggr (google it) if its set up using a formula dimension in a chart (in App OverView)

There are many sections on scripting. But it is now possible to do a lot of the fairly simple stuff in Qlik Sense using  'Data Manager'. Just use Data Manager > Table > Edit > Add a field

https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-learn-Qlik-Sense-script/td-p/15591

Hope the above helps

[Coolavin]

Thanks for focusing on just one thing I said in my last post and ignoring everything else. 

And thanks for being vague about what SCRIPT is. 

Also, I never said I was using a measure. I have been talking about a dimension the whole time. Not sure where you are getting the idea of a measure from.. Kind of bizzard that you would randomly bring up a measure now. 

and you clearly ignored the AGGR I was using in my original expression:

=If([CredentialExpireDate] < Today(), 'Inactive', If([CredentialExpireDate] >= Today(), 'Active', If(aggr(alt(count(CredentialExpireDate),0),primarykeyfieldofothertable)=0,'Inactive', 'Active')))

AGGRs don't work either. Either the filtering is broken or qlik can't handle fields with missing values. Instead of just going around in circles just be upfront and let people know that. 

I am evaluating if our clients can do everything they need to do in qlik sense with the custom reporting. It's clear that they can't. 

I'll go ahead and let my management know that qlik can't handle basic things like blank fields as well as how "helpful" people are on their forums. 

[robert99]

"AGGRs don't work either."

They do if you use them correctly in a dimension. And you haven't as far as I can see.

You need to calm down (take some time away from this) and then read again CAREFULLY what I have written. And then try what I have recommended. This in my experience is the best way to get rid of blank or empty (as opposed to null) fields. To do it in the data load sections not the charts.

And your way is so confusing that I haven't a clue why you're doing it. You may have a good reason for doing this though but if so its beyond my understanding. And then you need help from someone else. But maybe around Christmas and new year is not the best time to get this help.

[Coolavin]

Thanks again for providing zero meaningful information!

You are going out of your way to not read what I post. I never said AGGRs don't work in the dimension. Infact I stated that the AGGR works. It's the filters that don't work and I pointed out that either the filters are broken or how the Status field is populated doesn't work with filters. 

Again since you keep ignoring what I post, like I said before we are not going to be doing this in a data load. This is an evaluation of what are clients will have access to do and we will not be giving them access to load the data. Qlik is clearly a broken system thanks for pointing that out. 

You are the one that needs to take some time away from this. Please stop responding to my posts.