topic Re: Dynamic variable value issue in QlikView

Dynamic variable value issue

Anonymous — Tue, 16 Jun 2015 13:08:17 GMT

Hi All,

I am having issue with below code,it creates dynamic variables,but its assigning wrong text from next variable.

Help:

LOAD ChartName,
     VariableName,
     Text,
     Section

FROM
[..\..\Source_Files\Bonus\Help\Text.QVD](qvd);

LET nRows = NoOfRows('Help')-1;
FOR i = 0 to $(nRows)
let vVariable = FieldValue('VariableName',$(i));
let $(vVariable) = FieldValue('Text',$(i));
NEXT i

Please any help will be appreciated

Thanks

Neetha

Re: Dynamic variable value issue

giakoum — Tue, 16 Jun 2015 13:17:53 GMT

most likely you have repeating values between rows

use lookup instead of fieldvalue

Re: Dynamic variable value issue

giakoum — Tue, 16 Jun 2015 13:19:27 GMT

Note the Note

FieldValue(fieldname , n )

Returns the field value found in position n of the field fieldname (by load order). fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes. The first field value is returned for n=1. If n is larger than the number of field values, NULL is returned.

Note: This function will only work with distinct field values.

Example:

FieldValue( 'HelpText', 5 )

Re: Dynamic variable value issue

Anonymous — Tue, 16 Jun 2015 13:24:43 GMT

Hi Ioannis,

all values are distinct in the file.

Thanks

Neetha

Re: Dynamic variable value issue

giakoum — Tue, 16 Jun 2015 13:29:08 GMT

maybe in a row level

but not in field level. you have fields that have not unique values

Re: Dynamic variable value issue

giakoum — Tue, 16 Jun 2015 13:29:19 GMT

post the file if possible

Re: Dynamic variable value issue

Anonymous — Tue, 16 Jun 2015 13:45:32 GMT

hi ioannis,

yes you are right section field has duplicates.

its not possible to upload file

Thanks

Neetha

Re: Dynamic variable value issue

avinashelite — Tue, 16 Jun 2015 13:52:10 GMT

Hi neetha,

Can you please tell what is the requirement ? because for the same variable your assigning two value one with text and one with variable name, so the combination may be unique but not the individual column elements . If that is the case try to build a composite key out of both and while extracting you can divide the composite key and use the variable

Re: Dynamic variable value issue

giakoum — Tue, 16 Jun 2015 13:53:57 GMT

then you cannot use FieldValue. you need to use Lookup instead.

Re: Dynamic variable value issue

Tue, 16 Jun 2015 13:56:11 GMT

can anybody post any example of lookup

thanks

Re: Dynamic variable value issue

giakoum — Tue, 16 Jun 2015 14:08:21 GMT

lookup(fieldname, matchfieldname, matchfieldvalue [, tablename])

Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the field matchfieldname.

Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).

The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.

Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.

If no match is found, null is returned.

Example:

lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

Re: Dynamic variable value issue

giakoum — Tue, 16 Jun 2015 14:11:20 GMT

Please close this discussion if you solved your problem adequately.

Re: Dynamic variable value issue

Tue, 16 Jun 2015 14:13:08 GMT

to use lookup, you would need to create a rowno() or some such field in the base table for your "i" variable to match against in the lookup.

Another option would be to use peek instead

hope that helps

Joe

Re: Dynamic variable value issue

giakoum — Tue, 16 Jun 2015 14:14:37 GMT

duplicates are only in section field. So the remaining 3 fields may be used as lookup.

Re: Dynamic variable value issue

Tue, 16 Jun 2015 14:24:41 GMT

What I mean is, in order to do the required loop through, you need a value to use during each iteration of your lookup.

For this you would need a rowno to match against the i variable already created.

or create a concat list of 'VariableName' to for loop over.

Just pointing out you would need a bit of extra coding to use lookup that's all, where as peek could just be substituted in with ease to code already posted.

let vVariable = Peek('VariableName',$(i));
let $(vVariable) = Peek('Text',$(i));

Joe

Re: Dynamic variable value issue

Anonymous — Tue, 16 Jun 2015 16:10:05 GMT

Hi All,

Thanks for all suggestions Peek function resolved issue:

Help:
LOAD ChartName,
     VariableName,
     Text,
     Section
FROM
[..\..\Source_Files\Bonus\Help\Text.QVD]

(qvd);

LET nRows = NoOfRows('Help')-1;
FOR i = 0 to $(nRows)
let vVariable = Peek('VariableName',$(i));
let $(vVariable) = Peek('Text',$(i));
NEXT i

Thanks

Neetha