topic Re: Using peek() function in data load as name of column in App Development

Using peek() function in data load as name of column

Ondrism — Mon, 30 Jan 2023 10:36:45 GMT

Hi there,

I have a problem. I am loading a dynamic names of fields. From this script i want to use all names (saved i ntable NameOfFields) to load data from previous table (FinalTable_Temp). But there is the problem, the script returns that, all fields are same as the result of PEEK() function. I want to use PEEK() function to return a NAME of column which returns values from table (FinalTable_Temp) and not use PEEK() function as value. Peek() highlighted below:

for field = 0 to NoOfRows('NamesOfFields') - 1

if IsNum(peek('FieldName',$(field),'NamesOfFields')) then

vEditedField = '"' & date(peek('FieldName',$(field),'NamesOfFields')) & '"';

Else

vEditedField = '"' & peek('FieldName',$(field),'NamesOfFields') & '"';

end if

If $(field) = 0 then

FinalTable:
Load
SKU as SKUKey,
peek('FieldName',$(field),'NamesOfFields') as $(vEditedField)
Resident FinalTable_Temp;

else

FinalTable:
Left Join
Load
SKU as SKUKey,
peek('FieldName',$(field),'NamesOfFields') as $(vEditedField)
Resident FinalTable_Temp;

end if

NEXT field

Maybe is there another solution to solve this problem, but i prefer to use this. Thanks you for the support,
Ondra Mach

Re: Using peek() function in data load as name of column

jbhappysocks — Mon, 30 Jan 2023 11:13:46 GMT

Hi! I might be misunderstanding something, but if not I believe

Load
SKU as SKUKey,
$(vEditedField)
Resident FinalTable_Temp;

should give you the output you want? You don't need the peek()

Re: Using peek() function in data load as name of column

Ondrism — Mon, 30 Jan 2023 11:46:35 GMT

Problem is, that i need to rename fields name to Date format so firstly use number to load data from previous table a then use the same peek value and trasform it to date and name column after it. Another problem is as you can see in the picture - every value is the same as filed name (result of peek function). Mine script can rename fields to date, but the values are still the number format of that (result of peek function).

Re: Using peek() function in data load as name of column

Ondrism — Mon, 30 Jan 2023 11:52:18 GMT

Main result is this:

Re: Using peek() function in data load as name of column

marcus_sommer — Mon, 30 Jan 2023 11:58:09 GMT

You don't need such complicated approach to load your data else you could just load + transform with:

The Crosstable Load - Qlik Community - 1468083

Re: Using peek() function in data load as name of column

Ondrism — Mon, 30 Jan 2023 12:04:40 GMT

i am going to try it, thanks for the tip. I'll let you know if it works.

Re: Using peek() function in data load as name of column

jbhappysocks — Mon, 30 Jan 2023 12:27:51 GMT

Crosstable might be very well be what you're after. But to achieve your output table with date in headers you can add/update bold parts:

for field = 0 to NoOfRows('NamesOfFields') - 1

let vField = peek('FieldName',$(field),'NamesOfFields');

if IsNum(peek('FieldName',$(field),'NamesOfFields')) then

vEditedField = '"' & date(peek('FieldName',$(field),'NamesOfFields')) & '"';

Else

vEditedField = '"' & peek('FieldName',$(field),'NamesOfFields') & '"';

end if

If $(field) = 0 then

FinalTable:
Load
SKU as SKUKey,
$(vField) as $(vEditedField)
Resident FinalTable_Temp;

else

FinalTable:
Left Join
Load
SKU as SKUKey,
$(vField) as $(vEditedField)
Resident FinalTable_Temp;

end if

NEXT field

Will give you the Date as header and field value as value

Re: Using peek() function in data load as name of column

Ondrism — Mon, 30 Jan 2023 12:39:36 GMT

Thanks for the tip, I'll try it later when I have time. For now I managed to solve it much easier by using rename filed:

FOR Column = 1 to NoOfFields('FinalTable_Temp')

vEditedField = FieldName($(Column),'FinalTable_Temp');

If isnum('$(vEditedField)') then

vEditedFieldDate = date(FieldName($(Column),'FinalTable_Temp'));
Rename Field '$(vEditedField)' to '$(vEditedFieldDate)';

end if

NEXT Column

Re: Using peek() function in data load as name of column

Ondrism — Tue, 31 Jan 2023 11:05:06 GMT

Hi, a reworked the code - now is no need to use peek() function, and it works perfect:

let j=0;
FOR Column = 1 to NoOfFields('FinalTable_Temp')

vEditedField = FieldName($(Column),'FinalTable_Temp');

If isnum('$(vEditedField)') then

vEditedFieldDate = date(FieldName($(Column),'FinalTable_Temp'));
Rename Field '$(vEditedField)' to '$(vEditedFieldDate)';

j = j + 1;
let vField$(j) = '[' &FieldName($(Column), 'FinalTable_Temp') & ']';

if j = 1 then

FinalTable:
LOAD
SKU as SKUKey,
num($(vField$(j))) as $(vField$(j))
Resident FinalTable_Temp;

else

Left Join(FinalTable)
Load
SKU as SKUKey,
num($(vField$(j))) as $(vField$(j))
Resident FinalTable_Temp;

End If

else

j = j + 1;
let vField$(j) = '[' &FieldName($(Column), 'FinalTable_Temp') & ']';

if $(Column) = 1 then

FinalTable:
LOAD
SKU as SKUKey,
$(vField$(j)) as $(vField$(j))
Resident FinalTable_Temp;

else

Left Join(FinalTable)
Load
SKU as SKUKey,
$(vField$(j)) as $(vField$(j))
Resident FinalTable_Temp;

end if

end if

NEXT Column

Drop Field SKUKey;
drop Table FinalTable_Temp;

anyway, thanks you!