topic Help with Custom Selections box in QlikView

Help with Custom Selections box

Thu, 08 Oct 2015 20:29:16 GMT

I have created a custom selections box due to my large dimension data schema and bad looking field names. However I am noticing a problem when I select a specific year I get two translations from my translation table (NewName (User friendly name), OldName(This is the database name)) because my underlying fields have a similar name of YearNBR and FiscalYearNBR. Qlikview is seeing the selection of FiscalYearNBR as both of those and returning multiple translations. The formula I use is below for the current selection box translated name from the database name. Is there a way to force this formula to look for an exact string match?

Formula used to grab current selections and translate:

= if (index(vCurrentSelections,only({1}$Field) & ':')>0,

textbetween(
concat(TOTAL DISTINCT OldName & ':' & NewName,'|',OldName ) & '|'
,only({1}$Field) & ':', '|')
)

Forumla used to grab current selection values:

=TextBetween( vCurrentSelections & '|', only({1}$Field) & ':', '|')

formula for vCurrentSelections:

=GetCurrentSelections('|',':',',')

I am seeing this in my current selections box

2015-10-08_1526 - jeweinb1's library

Re: Help with Custom Selections box

Gysbert_Wassenaar — Fri, 09 Oct 2015 15:09:23 GMT

Can you post a small qlikview document that illustrates the problem?

Re: Help with Custom Selections box

Mon, 19 Oct 2015 19:20:00 GMT

Hey Gysbert,

sorry for the late response here is a small app that is showing my issue. I reworked some of the formulas but still getting the same problem.

Re: Help with Custom Selections box

Oleg_Troyansky — Mon, 19 Oct 2015 22:02:37 GMT

Hi Jason,

You nailed it yourself, - this problem is the result of the two field names being very similar. Specifically, one field name is included completely in the other.

When you are comparing the "old" field names with the selected Fields, the name "YearNBR" satisfy two conditions at once - it exists in the field name "YearNBR" and also in the field name "FiscalYearNBR", therefore causing the confusion.

The simple "patch" to your problem is to rename "YearNBR" to something different, like "CalendarYearNBR".

A more fundamental question is - why not use your existing "map" of old names and new names and simply rename all the fields at the end of your load script? All you need to do is to load the pairs of "old" and "new" fields into a mapping table (call it Fields_Map) and then rename all the fields in a single statement:

RENAME FIELDS Using Fields_Map;

You will save so much headache, including the custom Current Selections Box, by very little effort.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Help with Custom Selections box

Tue, 20 Oct 2015 12:13:01 GMT

Unfortunately I have already built out my Qlik app and I have 500 + fields in this app. Would changing the names above force me to update all the forumlas across the app?

Re: Help with Custom Selections box

raman_rastogi — Tue, 20 Oct 2015 12:47:15 GMT

It is not a good practices a bad code eat more time than development.

Re: Help with Custom Selections box

Oleg_Troyansky — Tue, 20 Oct 2015 16:45:26 GMT

Hi Jason,

Renaming 500+ fields at a time may or may no work, in terms of updating formulas... I'd try it out on a separate instance of your document.

Renaming a single field definitely works (unless you store your expressions in variables or in data tables). I'd think in terms of a programming a loop that could run 500 times, rename one field at a time and save the document. This way, you may get away from the mess that you are in now. Bottom line, you'd still save time on constant renaming in the UI...

Also, I recommend downloading Rob Wunderlich's Document Analyzer from www.qlikviewcookbook.com and analyzing your 500+ fields. I bet some of them are not being used.

good luck!

Oleg Troyansky