topic concat field and variable for a function parameter in fieldindex in App Development

concat field and variable for a function parameter in fieldindex

Morgoz — Mon, 31 May 2021 16:40:08 GMT

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.

Re: concat field and variable for a function parameter in fieldindex

marcus_sommer — Tue, 01 Jun 2021 07:44:10 GMT

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

Re: concat field and variable for a function parameter in fieldindex

Morgoz — Tue, 01 Jun 2021 21:51:07 GMT

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.

Re: concat field and variable for a function parameter in fieldindex

marcus_sommer — Wed, 02 Jun 2021 06:56:58 GMT

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

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

- Marcus