topic Re: a dynamic variable in App Development

a dynamic variable

FrankTheTank — Thu, 14 Mar 2019 13:56:23 GMT

hello everybody 🙂

i would generate a generic variable. is it possible to name the variable in step of a for next statement by the step?

Re: a dynamic variable

marcus_sommer — Thu, 14 Mar 2019 15:18:23 GMT

I would say yes - did you try it?

- Marcus

Re: a dynamic variable

FrankTheTank — Thu, 14 Mar 2019 15:24:27 GMT

of course 🙂 forgive me my imprecise question.
how can I make a variable number so dynamic that I get number1, number2, number3, and so on.

Re: a dynamic variable

marcus_sommer — Thu, 14 Mar 2019 15:29:18 GMT

I few more details to which input should results in which output would be really useful - do you mean something like:

for i = 1 to 5
let Number$(i) = $(i);
next

- Marcus

Re: a dynamic variable

FrankTheTank — Thu, 14 Mar 2019 15:36:33 GMT

Yeah, kind of like that. I read all the fields of a line from a table and join them into a string to make it a hash. I would like to do this for every line of the table and I wanted to save the hash values per line in a dynamically named variable and then put them in a table.

Re: a dynamic variable

marcus_sommer — Thu, 14 Mar 2019 15:52:33 GMT

Ok. all fieldvalues of a record should be string-concatenated and then be hashed - and for what is here a variable needed?

- Marcus

Re: a dynamic variable

FrankTheTank — Thu, 14 Mar 2019 15:58:22 GMT

set vTable = 'Test';
set vFile = 'Test.XLSX';
let vFileName = Replace(SubField('$(vFile)','/',-1),'.XLSX','');
let vFileDate = FileTime('$(vFile)');

[$(vTable)]:
LOAD
*
FROM [$(vSource)/$(vFile)]
(ooxml, embedded labels);

Let vFieldsConcatenated=;

FOR i = 1 to NoOfFields('$(vTable)')
Fields:
LOAD
FieldName($(i),'$(vTable)') AS FieldName
AutoGenerate 1
;
Let vFieldsConcatenated='$(vFieldsConcatenated)'&If($(i)>1,',')&Peek('FieldName');
NEXT i

Object:
LOAD
*,
'$(vHash)' as HashMkeyNum,
today() as TS
Resident [$(vTable)];
drop table [$(vTable)];

I now thought that I somehow calculate the hash value for each line and store it in a variable to load it later into the table. Or am I wrong?

Re: a dynamic variable

marcus_sommer — Thu, 14 Mar 2019 16:59:37 GMT

I must admit that I could not completely comprehend your logic where and why you want to create multiple variables which then should contain the hashes and should be then loaded into a table. I wouldn't do that else I would try to create everything possible within a table-load (and this is for me a general approach and often much easier than the variable-handling) and applying the hash-logic directly within the origin load. Maybe with something like this:

t1: first 1 LOAD * FROM [export.XLSX] (ooxml, embedded labels, table is Sheet1);

t2: load peek('F') & '[' & evaluate('fieldname(' & recno() & ', ''t1'')') & ']' & if(recno() < nooffields('t1'), '&''|''&') as F
autogenerate nooffields('t1');

let h = peek('F', -1, 't2'); drop tables t1, t2;

t3: load recno() as RecNo, hash256($(h)) as Hash, autonumberhash256($(h)) as Autonumberhash
from [export.XLSX] (ooxml, embedded labels, table is Sheet1);

- Marcus

Re: a dynamic variable

FrankTheTank — Thu, 14 Mar 2019 17:19:45 GMT

i will think over it tomorrow and will reply you what my problems are.
thanks

Re: a dynamic variable

FrankTheTank — Fri, 15 Mar 2019 07:34:55 GMT

Good Morning everybody.
i have tested your Script and it doesn´t work. the evaluate() function returns NULL for every entry.
I don´t know why. These all are Text Fields from Excel.

I have tested my script again and it works. Except for two columns with an "/" in it. this won´t work when i concat the filds of a row.

but i will thank you for your help and hope you can give me a hint to the solution 🙂
best regards

Re: a dynamic variable

marcus_sommer — Fri, 15 Mar 2019 07:54:03 GMT

For me with QV 11.2 it worked. With another release the handling of fieldname() and/or with the quotes might have slightly changed and the workaround with evaluate() might be now unnecessary - are you sure that you took my code-snippet 1:1? If not there might be a small syntax-issue.

- Marcus

Re: a dynamic variable

FrankTheTank — Fri, 15 Mar 2019 08:17:56 GMT

yes, I only changed the source. But I use Qlik Sense. does it matter?

Re: a dynamic variable

marcus_sommer — Fri, 15 Mar 2019 09:21:36 GMT

The general workings should be the same but the release (of the engine) is surely different. I don't know if it's in your case worth to search for the cause - maybe it's just the legacy-mode or something similar - but I'm sure that these logic could be applied.

- Marcus

Re: a dynamic variable

FrankTheTank — Fri, 15 Mar 2019 09:30:07 GMT

i have testet your script without the evaluate function.

the same Problem.

t1:
first 1 LOAD * FROM [lib://OjO/OJO.XLSX](ooxml, embedded labels, table is OJO);


t2:
load peek('F') & '[' &
    	fieldname(' & recno() & ', 't1')
        & ']' & 
    if(recno() < nooffields('t1'), '&"|"&') as F
autogenerate nooffields('t1');

to see what is in 'F'.

Re: a dynamic variable

marcus_sommer — Fri, 15 Mar 2019 10:14:03 GMT

For me it worked:

Before going further just check if the normal fieldname() function itself worked, with:

fieldname(1, 't1')

if yes, try it with:

fieldname(recno(), 't1') and then with:

fieldname(evaluate(recno()), 't1') or fieldname(eavluate('recno()'), 't1')

and if this don't worked with:

evaluate('fieldname(' & recno() & ', ''t1'')')

whereby the exact use of the quotes is important. An alternatively to the last could be:

evaluate('fieldname(' & recno() & ',' & chr(39) & 't1' & chr(39) & ')')

- Marcus

Re: a dynamic variable

FrankTheTank — Fri, 15 Mar 2019 12:07:12 GMT

fieldname(1, 't1') => works

fieldname(recno(), 't1') => works

fieldname(evaluate(recno()), 't1') => works

fieldname(evaluate('recno()'), 't1') => everything is NULL

evaluate('fieldname(' & recno() & ', ''t1'')') => every entry is "F"

evaluate('fieldname(' & recno() & ',' & chr(39) & 't1' & chr(39) & ')') => works

if I take the last way in your script in further steps i get an error.

Re: a dynamic variable

marcus_sommer — Fri, 15 Mar 2019 14:21:45 GMT

This is the desired approach:

fieldname(recno(), 't1')

and everything else with the evaluate-stuff was just a workaround for a seemingly strange bug/feature in 11.2.

- Marcus

Re: a dynamic variable

FrankTheTank — Mon, 18 Mar 2019 07:58:09 GMT

OK. But if I continue to use that is the error shown by me. what do I do about it?

But thank you in advance

Re: a dynamic variable

marcus_sommer — Mon, 18 Mar 2019 13:14:24 GMT

Which error - the above one from the screenshot? If yes it meant that the record-check within field-concat - recno() vs. nooffields() - didn't work properly. Why I don't know - but could it be that Qlik tries to load a column with a blank fieldname (this happens if there is any content within any record of this column).

- Marcus

Re: a dynamic variable

FrankTheTank — Mon, 18 Mar 2019 15:39:39 GMT

Yes, exactly this mistake. And yes, there is content in the columns. For each column, the program also does, for more than one column bicht. Is this due to a faulty understanding?