concat field and variable for a function parameter in fieldindex

[Morgoz]

Hello,

I need to concatenate a variable and a field value to be used inside a FieldIndex and then in a Peek function:

let t = date#('2021-03-18');

DQ_FACTS_QVD_INI_AUX:
LOAD 
DQ_ID,
FieldIndex('DQ_FACTS_ID_WITH_DATE', "DQ_FACTS_ID"&'$(t)')-1 as FINDEX, 
Peek('DQ_FACTS_ID_WITH_DATE',FieldIndex('DQ_FACTS_ID_WITH_DATE', "DQ_FACTS_ID"&'$(t)')-1,'DQ_FACTS_QVD_INI') as DQ_FACTS_ID_WITH_DATE
Resident DQ_FACTS_QVD_INI;

The problem is that FieldIndex(...) is not returning the correct position, it returns a position, but not the correct one... I don't know how is "DQ_FACTS_ID"&'$(t)' getting composed.

Thaks.

[marcus_sommer · Accepted solution]

It's not quite clear what do you want to achieve? The cause of the unexpected results is probably that you mix two quite different functions because fieldindex() is running against the (distinct values) within the system-tables and peek() fetched the results from the data-tables. Both tables are linked through a bit-stuffed pointer. Probably there aren't many scenarios in which you may mix up both logics - maybe if both table-areas contain the same number of distinct values otherwise you will need different approaches to get your data - maybe lookup() ?

- Marcus

[Morgoz]

Thank you very much Marcus,

with Lookup function it works perfect.

Sorry for the newbe question, but just to try to understand it better, what do you mean by "system-tables" and "data-tables"? Just want to understand where are FieldIndex and Peek exactly looking for the data.

Thanks again.

[marcus_sommer]

Here you will find some background how the data are handled internally:

Symbol Tables and Bit-Stuffed Pointers - a deeper ... - Qlik Community - 1490433

- Marcus