topic Re: Syntax for Zero-Length strings in QlikView

Syntax for Zero-Length strings

Fri, 29 Jun 2012 20:02:24 GMT

Our CRM database exports a "zero-length string" (ZLS) for fields which are empty, but not NULL. My question is - how do I exclude this in a trigger? I tried using <FieldName = {"(Bob|Mary|Ted)"}-{""}>, but that didn't work. Anyone know of a solution?

I also tried setting the trigger to, on sheet activation, clear all selections, then clear this field specifically, then apply just the selections I want, but the ZLS entries still show up. Then, I created a bookmark of just the selections I want, went to the sheet triggers, selected "Apply Bookmark" and the bookmark name, but again the ZLS entries show up. However, if I just go on the page, and select the bookmark, the ZLS entries don't show up. Is there a difference between the way bookmarks operate on the page, and in triggers?

Finally, am I correct in assuming that trigger precedence is top down (I kinda thought that was implied from the "Promote"/"Demote" buttons, but I'm not sure).

Thanks

Re: Syntax for Zero-Length strings

deepakk — Fri, 29 Jun 2012 20:32:22 GMT

hi,

Try this method. While loading the data give and identification to ZLS like

if(len(trim(FieldName)) =0,'NA',FieldName) as FieldName,

and in expression use this set analysis

I hope this helps.

Deepak

Re: Syntax for Zero-Length strings

nagaiank — Fri, 29 Jun 2012 20:34:59 GMT

If you do not need the rows having ZLS value, you may exclude those rows while loading your CRM data using script like the following:

LOAD * where Len(Trim(ZLSField)) > 0;

LOAD * From (your source);

If you want to handle and process the rows having ZLS value, you may replace the ZLS value with a non-ZLS value during the load using a script like the following:

LOAD *,If(Len(Trim(ZLSField)) > 0, ZLSField,'ZLS') as NewZLSField;

LOAD * From (your source);

Hope this helps.

Re: Syntax for Zero-Length strings

Fri, 29 Jun 2012 21:09:04 GMT

Thanks to both of you; neither worked though.

However, krishna, I noted that you had your LOAD statements as:

LOAD *,If(Len(Trim(ZLSField)) > 0, ZLSField,'ZLS') as NewZLSField;

whereas in my script I have the following:

LOAD

Month(Opp_StartDate) As StartMonth,

Year(Opp_StartDate) As StartYear,

If(Len(Trim(ZLSField)) > 0, ZLSField,'ZLS') as NewZLSField,

* ;

which works to the extent that the entries now do come in marked "NA". However, when I then went into my trigger and added the condition -{"NA"}, these entries still showed up the page.

Re: Syntax for Zero-Length strings

deepakk — Sat, 30 Jun 2012 07:16:38 GMT

HI,

Instead of set analysis , try the if statement.

Sum(If(Fieldname <> 'NA', Amount)).

if still doesn't work , try to attach a sample file , so that we can take a closer look into the issue.

Deepak