topic Re: Why does this expression work in one instance and not the other? in QlikView

Why does this expression work in one instance and not the other?

Fri, 20 Dec 2013 16:23:58 GMT

I am rather new to Qlikview and have figured out a number of techniques along the road, but I have been struggling with what I thought was a rather simple expression for two days now, and I am hoping someone can help me understand why this is not working.

A little background:

I have a document with approximately 150 fields (some of which are not well-named for the end users. To help manage the field labels, I created an Excel file with all 150 field names, and a corresponding Field-Alias that I load as part of the script ... the field aliases are then used on my listboxes and chart columns using the following syntax:

=Only({<MapThisField={$(=GetCurrentField(cycl_DigitalAnalysis0))}>} FldAlias_B)

...on this one I have a cyclic field that has 8 items in it .... 7 of them are other fields and 1 is an expression. And I get the field alias as expected for the 7 real fields, but my struggle surrounds the expression.

The expression starts with the term "=if(" so I thought I could test like this to handle that exception:

=if(GetCurrentField(cycl_DigitalAnalysis0) LIKE '*=if(*', 'Site/Product', '~OTHER~')

...and it works Great.

So why can't I get this to work?

=if(GetCurrentField(cycl_DigitalAnalysis0) LIKE '*=if(*', 'Site/Product', Only({<MapThisField={$(=GetCurrentField(cycl_DigitalAnalysis0))}>} FldAlias_B) )

I have tried various ways of testing for NULL and nothing I do seems to provide me with a solution. Even in the latter expression, it works just fine for the 7 real fields, but I continue to get "-" for that 8th field/expression.

Why? Can anyone help me understand this? If samples 1 and 2 above work fine, why wouldn't #3?

Right now I do not have a trimmed-down example document I could share, but if it becomes necessary, I can mock something up and post it. Hopefully someone can spot my short-sightedness in the above posted expressions.

Any help would be greatly appreciated.

UPDATE TO ORIGINAL POST - 12/20/2013 @ 2:19pm EST

I have now added a sample document to help illustrate the issue.

* Although I do trust Henric's alternative suggestion, this will still bother me until I can at least understand WHY. The syntax seems to work correctly in one instance, but not the other.

Re: Why does this expression work in one instance and not the other?

Fri, 20 Dec 2013 16:28:53 GMT

Hi Chris,

Try the Match function..

Re: Why does this expression work in one instance and not the other?

Fri, 20 Dec 2013 16:49:44 GMT

Raja,

I appreciate the quick reply. I am not seeing any difference in the results using that approach, if I understood you suggestion correctly, here is what I tried. As with the examples in the original post, it works good when the ELSE portion is a simple string '~OTHER~', but not when it is replaced with the other expression (that works fine on it's own and for the 7 other real fields).

if ( WildMatch (GetCurrentField(cycl_DigitalAnalysis0), '*=if(*'),'Site/Product', '~OTHER~')

...this works fine.

if ( WildMatch (GetCurrentField(cycl_DigitalAnalysis0), '*=if(*'),'Site/Product', Only({<MapThisField={$(=GetCurrentField(cycl_DigitalAnalysis0))}>} FldAlias_B))

...but I still have that same problem when I exchange the ELSE portion with the ONLY() function.

Re: Why does this expression work in one instance and not the other?

hic — Fri, 20 Dec 2013 16:53:23 GMT

With your method, every field label will be recalculated client-side every time a user clicks. This is CPU-intensive and further - as you have noticed - your formulae are error-prone.

No, you should instead use the "Rename" statement in the end of the script to rename everything according to your Excel file. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/25/how-to-rename-fields

HIC

Re: Why does this expression work in one instance and not the other?

Fri, 20 Dec 2013 16:58:12 GMT

Thank you Henric. I agree with your suggestion whole-heartedly, but I was trying to prevent having to rebuild the whole document with the renamed fields, so I was hoping to keep the original names in place and show an alias name in certain areas ... but I'll have to admit, it does complicate managing things, especially with so many fields. Not to mention the over-head.

Re: Why does this expression work in one instance and not the other?

hic — Fri, 20 Dec 2013 17:02:25 GMT

Oh, you don't need to rebuild anything. That should work straight off. Normally objects pick up a changed field name automagically

Try it!

HIC

Re: Why does this expression work in one instance and not the other?

Fri, 20 Dec 2013 17:15:26 GMT

Hi Chris,

Sry, Give me little time... I am trying it and update you...

Re: Why does this expression work in one instance and not the other?

Mon, 23 Dec 2013 22:15:46 GMT

I finally stumbled into the answer for this question by trial and error, so in case anyone else is interested, here was the solution:

Created Variable: vTEST

vTEST content: [=Only({<MapThisField={$(=GetCurrentField(cycl_DigitalAnalysis0))}>} FldAlias_B)]
...(without the brackets --- and note, the leading equal sign is important!)

Which now allows this expression to function as expected --- see original post for comparative differences.

=if(GetCurrentField(cycl_DigitalAnalysis0) LIKE '*=if(*', 'Site/Product', vTEST)

Re: Why does this expression work in one instance and not the other?

qlikpahadi07 — Tue, 24 Dec 2013 08:38:42 GMT

Hi Christopher,

I tried your solution too but not working don't know why, Interested to see if possible can you attach a sample will be helpful just make some changes with the demo App you already shared .

cheers!!!!

Re: Re: Why does this expression work in one instance and not the other?

Tue, 24 Dec 2013 19:22:27 GMT

QlikPahadi07. Sorry for the delay in reply. Here is an updated version of the QVW with a working example of the solution added below the original question scenario.

Re: Why does this expression work in one instance and not the other?

qlikpahadi07 — Wed, 25 Dec 2013 04:41:35 GMT

Thanks For Sharing Chris

Merry Christmas!!!!