a dynamic variable

[FrankTheTank]

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?

VG

[marcus_sommer]

I would say yes - did you try it?

- Marcus

[FrankTheTank]

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.

[marcus_sommer]

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

[FrankTheTank]

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.

[marcus_sommer]

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

- Marcus

[FrankTheTank]

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?

[marcus_sommer · Accepted solution]

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

[FrankTheTank]

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

[FrankTheTank]

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