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

[Ondrism]

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

[jbhappysocks]

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()

[Ondrism]

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).

[Ondrism]

Main result is this:

[marcus_sommer]

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

The Crosstable Load - Qlik Community - 1468083

[Ondrism]

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

[jbhappysocks]

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

[Ondrism · Accepted solution]

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

[Ondrism · Accepted solution]

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!