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]

Is it blank. Or null

Blank is when the CredentialExpireDate] field just doesn't have a date in it. Null is often the result of a join, So the fact table is joined to a dimension table where the join field is missing

I get rid of nulls and blank in the script.

Either using mapping load (nulls).

Or len for blanks

If (Len(Trim([CredentialAchievedDate]) > 0),'Active','Inactive') as CredentialAchievedDateType ,

If (Len(Trim([CredentialAchievedDate]) > 0),'CredentialAchievedDate,'Inactive') as CredentialAchievedDate,

But in measures (Ive always done this in script)

Have you tried " ".  As '*' equals *

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

or just (unsure if this counts nulls as well or not)

=COUNT( [CredentialMeasure])

or for the dimension try aggr

aggr(If (Len(Trim([CredentialAchievedDate]) <>0),'Active', 'Inactive'),******)

[Coolavin]

They are neither blanks or nulls they are empty fields. That's the problem. 

The expression below can find the empty fields in a measure.

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

I am unable to find these empty fields in a dimension.

I tried what you suggested and neither of them work. It still doesn't place "Active" in the field for the records that have an empty field in "CredentialExpireDate"

If (Len(Trim([CredentialExpireDate]) > 0),'Active','Inactive')

aggr(If (Len(Trim([CredentialExpireDate]) <>0),'Active', 'Inactive'),******)

Sorry it should be "CredentialExpireDate" not "CredentialAchievedDate" These are the original expressions I have tried. 

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

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

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

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

[Coolavin]

The expression below fills out the fields that are empty in "CredentialExpireDate" however when I try to filter on that column and select "Active" it ignores the records that are empty in "CredentialExpireDate" and will lose about 200k records. 

If(aggr(alt(count(CredentialExpireDate),0),primarykeyfieldofothertable)=0,'Inactive', 'Active')

So I need a way to filter off that populated column that can filter all of the "Active" records. 

[robert99]

@Coolavin t

You really need to give more information of what you are trying to achieve.

Including

-What data columns you have

-What the issue is (I'm still not sure)

-What chart with dimensions and measures you are trying to create

Keep it simple though. As if its too complex helpers just switch out.

[Coolavin]

The only columns I have are "CredentialExpireDate" and a column called "Status". "Status" that gets filled with an "Active" or an "Inactive" text if "CredentialExpireDate" is less than todays date then it should be "Inactive" if it's greater than or equal todays date then it should be "Active" and if it's empty it should be "Active".

The issue is that when I try to filter the Status column by "Active" it should have 1,014,047 records however it only shows 808,416 even though there are actually 1,014,047 records with "Active" in the Status column. The 205,631 that it loses are a group that has an empty field for the "CredentialExpireDate" column. So, I am guessing it has something to do with that. 

The records that are empty in "CredentialExpireDate" are not nulls or blanks, they are empty. The only way I have been able to populate the Status column of those records that are empty in the "CredentialExpireDate" column is with the expression below:

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

It works great with populating the Status column but the filters don't work. 

I need the filters to show all 1,014,047 Active records. 

I hope this explains it well enough. 

[robert99]

"The only columns I have are "CredentialExpireDate" and a column called "Status". !

Are you sure these are the only two columns? What are the above two fields referring to. Say a policy number or CustNum etc

And is the Status a number calculated by Qlik or by the system you are extracting the data from?

NB. I assume the Status column as as follows

-  If the "CredentialExpireDate" has a Date then it is correctly showing Active or Inactive

- If the "CredentialExpireDate" has no data then it shows as empyt

If the latter what I suggest is adding another Qlik calculated column as follows IN SCRIPT

if(lens(Status) >0 , Status, 'Active') as StatusAdjusted ... and then work with the StatusAdjusted column

But give me the details I need and I will show you how to get these details in a dimension column rather than in Qlik

[Coolavin]

Yes, there are only two columns. 

The Status column is not a number and is not calculated. It is a string with either "Active" or "Inactive" (see the image below)

The Status column is not empty, it either has "Active" or "Inactive" it is the "CredentialExpireDate"(Fiscal Date Expire) column that has empty fields. The Status column gets populated based on what is in the "CredentialExpireDate"(Fiscal Date Expire) column. 

The expression below doesn't work.  The Status column doesn't have any empty fields so all it does is populate the column with Active. 

if(lens(Status) >0 , Status, 'Active') as StatusAdjusted

If I change it to:  if(lens(CredentialExpireDate) >0 , 'Inactive', 'Active')  then it give everything an "Inactive" except the 205,631 with empty fields. It just ignores them. 

This expression below populates everything correctly even the records with an empty field in "CredentialExpireDate".

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

However when I try to filter by using the magnifying glass in the image below on the status column it ignores any record that is empty in CredentialExpireDate(Fiscal Date Expire) column even though I am not filter on the CredentialExpireDate(Fiscal Date Expire) column. Is there away to just filter the text values of the status column and ignore the formula/expression in it? or populate a new column with just the text values in status and then filter on the new column?

[robert99]

"it is the "CredentialExpireDate"(Fiscal Date Expire) column that has empty fields."

So why not (this is a question as I still unsure what you are trying to do) correct this using SCRIPT as explained below

if(lens("CredentialExpireDate") >0 , "CredentialExpireDate", 'Blank') as "CredentialExpireDateAdj"

Then you just need to use the two dimension fields (not measures) in the chart table

"CredentialExpireDateAdj"                    Status

Dimension not  a measure for both

[Coolavin]

Thank you for trying to help.

I am not sure how else to explain what I am trying to do. I need to be able to filter by the status column. It's not filtering correctly. So, that is what I am trying to do filter on the status column. I am not sure how else I can explain this to make it make any more sense. 

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

If I use the expression you gave: if(lens("CredentialExpireDate") >0 , "CredentialExpireDate", 'Blank') as "CredentialExpireDateAdj"

It gives me an invalid dimension error. If I remove AS "CredentialExpireDateAdj" and change lens to len then it no longer gives me an error, however, it never adds the Blank text it only picks up on the records that have something in "CredentialExpireDate"

Again: I am trying to filter on the status column, the status column gets populated based on what is in the CredentialExpireDate column, the filter ignores anything in the status column that has an empty field in the CredentialExpireDate column and it shouldn't. My issue is with the filtering but I assumed it had something to do with how I was populating the status column. Is there something else you can think of to get the filtering to work?