topic Re: app help - null fields in QlikView

app help - null fields

Fri, 04 Dec 2015 16:01:44 GMT

Hi,

We would like an app to populate an answer in a field if its blank and meets criteria. We have tried adding two expressions to the script but neither works:

"e_mail",if(e_mail = "-", 'No email', 'Has email'),

"tency_end_dt", if(tency_end_dt = null, 'Current Tenancy', 'Previous Tenancy'),

Any suggestions/advice?

Much appreciated.

Chris

Re: app help - null fields

sunny_talwar — Fri, 04 Dec 2015 16:04:20 GMT

If those are null, try this:

If(IsNull(e_mail), 'No email', e_mail) as e_mail or

If(Len(Trim(e_mail)) = 0, 'No email', e_mail) as e_mail

Re: app help - null fields

marcus_sommer — Fri, 04 Dec 2015 16:05:31 GMT

Try it with: if(len(trim(YourField))=0, 'empty or null or only spaces', 'values available')

- Marcus

Re: app help - null fields

Mon, 07 Dec 2015 07:58:27 GMT

Hi,

I have tried both the above replacing "e_mail" in the script ; however, comes up with error "file not found <null>'

With both 'email' and tenancy end date' which were trying to add this to there is some values populated (date or an email) but many blanks ("-")

To clarify we are replacing the script below in bold with your suggested expressions?

Load
age,
AgeBand,
Area,
"category_id",
"Client No",
"Contact_method",
DateofBirth,
"Disability_summary",
"e_mail",if(e_mail = "-", 'No email', 'Has email'),
"Economic Status",
Estate,
Ethnicity,
Gender,
"Internet_access",
Latitude,
Longitude,
LongLat,
"Marital Status",
Nationality,
Neighbourhood,
"Preferred Language",
"Primary Client",
"Property_ID",
"PVP_Alert",
Religion,
"Rent Account Name",
"Sexual Orientation",
"Tenancy No",
"Tenant Type",
"tency_end_dt", if(tency_end_dt = null, 'Current Tenancy', 'Previous Tenancy'),
"tency_st_dt",
UPRN,
"Vulnerability level";

Chris

Re: app help - null fields

marcus_sommer — Mon, 07 Dec 2015 08:05:36 GMT

Now more specific, try:

...

if(len(trim((e_mail))=0, 'No email', 'Has email') as e_mail_CHECK,

if(len(trim(tency_end_dt))=0, 'Current Tenancy', 'Previous Tenancy') as Tenancy_CHECK,

...

- Marcus

Re: app help - null fields

PrashantSangle — Mon, 07 Dec 2015 08:09:20 GMT

Hi,

Try in two statement like

if(e_mail = "-" or len(trim(e_mail))=0, 'No email') as NO_Email,

if(len(trim(e_mail))>0, e_mail) as Has_Email,

Regards

Re: app help - null fields

daveamz — Mon, 07 Dec 2015 08:31:33 GMT

Hi Chris,

For an extra validation for email address field, you can use something like: If(Index(e-mail, '@'), 'Has email', 'No email')

If no '@' is found in the e_mail string, 0 is return, thus FALSE

Regards,

David

Re: app help - null fields

Mon, 07 Dec 2015 14:37:54 GMT

Hi all,

Thanks for your input. Tried If(Index(e_mail, '@'), 'Has email', 'No email'), and its worked.

However, with tenancy end date it doesn't work. Can anyone suggest a possible solution? In the date if the tenancy is still live/active then there is '-'. If the tenancy has ended a date exists in the data.

Chris

Re: app help - null fields

daveamz — Mon, 07 Dec 2015 14:59:26 GMT

Check Marcus's response. It should work fine.

Re: app help - null fields

Tue, 08 Dec 2015 10:44:23 GMT

HI David, Marcus,

Yes the expression works - thank you:

if(len(trim(tency_end_dt))=0, 'Current Tenancy', 'Previous Tenancy') as Tenancy_CHECK,

I will mark as correct but for our understanding could you just explain the logic of how this works....

Chris

Re: app help - null fields

marcus_sommer — Tue, 08 Dec 2015 11:12:48 GMT

Most often you didn't really want to check if a field(value) is NULL (the '-' within the table-charts is only a replace-char for NULL and could be changed within the tab presentations on the bottom left) else you want check if no (valid) value is available. With len(trim()) you caught not only NULL else empty strings (for example this: = '' is not NULL) and not printable chars like leading spaces or many types of special chars. Very good explanations could you find here:

NULL – The Invisible Nothing

Finding NULL

- Marcus