topic Re: sanity check... in QlikView

sanity check...

giakoum — Wed, 23 Jan 2013 09:36:10 GMT

Hi all.

I have a table with 5 values, and I need to concatenate those values forming a string. Since I am going to use this string to load from a table, the 5 values actually represent field names so I need to put them into brackets, because there are spaces in the names.

So I thought of looping through the table and construct the string like this :

SET sectionAccessKeyFields='';

FOR currentFieldNameIndex=1 TO FIELDVALUECOUNT('KeyField')

LET currentFieldName=FIELDVALUE('KeyField',$(currentFieldNameIndex));

SET sectionAccessKeyFields=$(sectionAccessKeyFields)[$(currentFieldName)];

The strange thing is that brackets for 1st field seem to just disappear (see red arrows in image) but not for the next 4 fields. I have found a workaround for this, so I need NOT a solution, merely an explanation why this is happening. The workaround I came up with is to start from a non-empty string. but do you have any idea as WHY this is happening? I have attached a file with both the problem and the workaround.

Thank you for your time.

Ioannis.

Re: sanity check...

sujeetsingh — Wed, 23 Jan 2013 09:43:15 GMT

I am nat able to get it ??

Re: sanity check...

giakoum — Wed, 23 Jan 2013 10:41:53 GMT

I am not sure what you mean... you did not understand the problem or...

Re: sanity check...

flipside — Wed, 23 Jan 2013 10:49:19 GMT

Hi Ioannis,

It looks like Qlikview is stripping off the square brackets when it picks up the first pass, probably because they are a reserved character in Qlikview language.

You can see this in action ...

SET sectionAccessKeyFields3=[PRODUCT TYPE ID];

SET sectionAccessKeyFields4=[PRODUCT TYPE ID][REGION ID];

SET sectionAccessKeyFields5=$(sectionAccessKeyFields3)[REGION ID];

The middle variable works fine, but the last variable strips off the brackets.

I tend to use LET instead of SET when building strings so as to avoid confusion ...

SET sectionAccessKeyFields=;

FOR currentFieldNameIndex=1 TO FIELDVALUECOUNT('KeyField')

LET currentFieldName=FIELDVALUE('KeyField',$(currentFieldNameIndex));

LET sectionAccessKeyFields='$(sectionAccessKeyFields)' & '[' & '$(currentFieldName)' & ']';

flipside

Re: sanity check...

giakoum — Wed, 23 Jan 2013 11:00:33 GMT

Hi flipside.

Thank you for your answer.

My guess also, brackets being a reserved character, but why does not the same apply to the second and subsequent pass? This is what really troubles me.

Ioannis.

Re: sanity check...

jagan — Wed, 23 Jan 2013 11:33:31 GMT

Hi,

Try this way

LOAD * INLINE [

KeyField

PRODUCT TYPE ID

REGION ID

AREA ID

SUBSALESUNIT ID

ACCOUNT ID

];

//This makes the brackets for the 1st field disappear

SET sectionAccessKeyFields = '';

FOR currentFieldNameIndex=1 TO FIELDVALUECOUNT('KeyField')

LET currentFieldName= '[' & FIELDVALUE('KeyField',$(currentFieldNameIndex)) & ']';

LET sectionAccessKeyFields= sectionAccessKeyFields & currentFieldName;

Hope this helps you.

Regards,

Jagan.

Re: sanity check...

giakoum — Wed, 23 Jan 2013 11:37:05 GMT

Hi Jagan.

thank you but I have found already a similar solution. I just need to know WHY QlikView behaves like this.

Regards,

Ioannis.

Re: sanity check...

jagan — Wed, 23 Jan 2013 11:43:29 GMT

HI,

I think it is evaluation the value for the first time, I am not sure about this.

Regards,

Jagan.