Table column names in Qlik App

[faandebruyn]

Hallo,

I have an application that visualize a table [KPIS]. The table has a few columns - KPIID, KPINAME, KPIDESCIPTION, "Jul 2021", "Aug 2021" .. "Jul 2022". This application is the base for an Excel Nprinting report, where the table image are attached as an Excel sheet.

The problem is that when the next month started, the table columns move 'on' one month - now from KPIID, KPINAME, KPIDESCIPTION, "Aug 2021" ... "Aug 2022". Now the application has an error, because it cannot find the column "Jul 2021" in the table, so the Nprinting report fails (or rather gives a warning "WARN: error during report generation: Exceeded maximum number of retries").

I changed my table to have columns KPIID, KPINAME, KPIDESCIPTION, 1, 2, 3, 4 ... 13, and a second table FIELD_NAMES, with columns "COLUMN_FIELD" & "COLUMN_LABEL", with the following values:

COLUMN_FIELD, COLUMN_LABEL

 KPIID   1,

KPINAME 2,

KPI_DESCRIPTION 3,

Aug 2021 4,

Sept 2021 5, etc.

I want to know how I can map my column labels in the table visualization to the COLUMN_FIELD value in FIELD_NAMES.

Any ideas?

Below is the application script:

Let vLastUpdateTime = QvdCreateTime('$(FUNC_DATA_DIR)/THE_DATA.qvd');

[PPOEE]:
Load * From [$(FUNC_DATA_DIR)/THE_DATA.qvd] (qvd);

[T2]:
Generic Load KPICODE As KPI_CODE,
KPIAREA As KPI_AREA,
KPITYPE As KPI_TYPE,
PRODMONTH,
VALUE
Resident PPOEE;

Set vListOfTables = ;

For vTableNo = 0 To NoOfTables()
Let vTableName = Tablename($(vTableNo));
If Match(Subfield(vTableName, '.', 1), 'T2') Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables) > 0, ',') & chr(39) & vTableName & chr(39);
End If
Next vTableNo

NoConcatenate
[PPOEE_FLAT]:
Load KPICODE As KPI_CODE,
KPIAREA As KPI_AREA,
KPITYPE As KPI_TYPE
Resident PPOEE;

For Each vTableName in $(vListOfTables)
Left Join (PPOEE_FLAT) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
next vTableName

[FIELD_NAMES]:
load fieldname(recno(), 'PPOEE_FLAT') as Field, RecNo() as FieldNum autogenerate nooffields('PPOEE_FLAT');

Let numFieldNames = NoOfRows('FIELD_NAMES');
For rowIdxI = 0 To numFieldNames - 1
Let vField = Peek('FieldNum', $(rowIdxI), 'FIELD_NAMES');
Let vLabel = Peek('Field', $(rowIdxI), 'FIELD_NAMES');

If Not Match(vLabel, 'KPI_CODE', 'KPI_AREA', 'KPI_TYPE' ) Then
Rename Field '$(vLabel)' to '$(vField)';
End If
Next rowIdxI

Drop Table PPOEE;

Below is a screenshot of my data

[Digvijay_Singh · Accepted solution]

May be something like this for the mapping you are trying to achieve - 

'$(=Only({<COLUMN_LABEL={1}>}COLUMN_FIELD))'

'$(=Only({<COLUMN_LABEL={2}>}COLUMN_FIELD))' and so on

[faandebruyn]

Thanks, I used your solution and it worked! I first had to rename my FIELD_NAMES columns to ColName & ColNum, because "Field" and "FieldNum" seems to be reserved words. But after that, I changed the table label to: 

=Only({<ColNum={4}>}ColName).

That worked! Thank you very much.