Is there a way to dynamically update column headers each month in Qlik Sense?

[MishaM]

I have an Excel file that is automatically dropped onto a Qlik server each week. Each month, the header columns in the file are updated by removing the oldest month and adding the current month. Below is an example of the headers in the file:

Model Std

Jan

Feb

Mar

Dec

Install Base

Each month the file fails to load automatically because of an error like the one below:

The following error occurred:

Field 'Sep' not found

The error occurred here:

[xxxx/xxxx]: CrossTable(FRMonth,FRPerc,2) LOAD [Model Std], [Install Base], [Sep], [Oct], [Nov], [Dec] FROM [lib://xxxx/xxxx/xxx.csv] (txt, codepage is 28591, embedded labels, delimiter is ',', msq)

Is there a way to dynamically or automatically update column headers each month in Qlik Sense?

[stevejoyce]

You can load the header row, then loop through fields in that table with some logic to pull our your non-standard columns (Model Std, Install Base) and build a variable that lists your "dynamic field names" and use that in your load script.  Like... 

                rawdata:

LOAD

*

FROM [lib://xxxx/xxxx/xxx.csv] (txt, codepage is 28591, embedded labels, delimiter is ',', msq)

where rowno() = 0;

let vDynamicFields = null();

for field_i = 1 to NoOfFields('rawdata')

                let vFieldName = fieldname($(field_i), 'rawdata');

    trace $(vFieldName);

    if '$(vFieldName)' <> 'Model Std'  then

                if len('$(vDynamicFields)') = 0 then

               let vDynamicFields = '[' & '$(vFieldName)' & ']';

        else

               let vDynamicFields = '$(vDynamicFields)' & ',' & '[' & '$(vFieldName)' & ']';

                end if

    end if

next field_i

trace $(vDynamicFields);

drop table rawdata;

dynamic_columns:

load

                             $(vDynamicFields)

FROM [lib://xxxx/xxxx/xxx.csv] (txt, codepage is 28591, embedded labels, delimiter is ',', msq);

exit script;

[aveeeeeee7en]

Hi @MishaM 

Try using variables. It will work in your case.

Regards,

Av7en

[rwunderlich · Accepted solution]

if you can rearrange the columns so Model Std and Install Base -- the non variable columns -- come first, then you can take care of the issue using Load *.

[xxxx/xxxx]: CrossTable(FRMonth,FRPerc,2) LOAD *
FROM [lib://xxxx/xxxx/xxx.csv] (txt, codepage is 28591, embedded labels, delimiter is ',', msq)

-Rob

[MishaM]

Thank you for all the responses. Is there any way to simplify or make the resolutions a little more explicit? I've never used variables in Qlik before and my scripting skills are nearly non-existent.

[rwunderlich]

My suggestion is to rearrange the csv file so the columns come in this order:

Model Std

Install Base

Jan

Feb

Mar

Dec

You already have a CrossTable Load statement. Replace the explicit field list in the load with "*" so it looks like this. 

CrossTable(FRMonth,FRPerc,2) LOAD * 
FROM [lib://xxxx/xxxx/xxx.csv] (txt, codepage is 28591, embedded labels, delimiter is ',', msq)

In this way additional months, or changes to the month headers, will get automatically picked up. 

-Rob

[Vegar]

If you can't make edits to the excel then you could even make advantage of @rwunderlich's approach

You can force the right order by creating an empty table with the first two column names that you later concatenate with the excel data.

[Data]: LOAD * INLINE [ 

Model Std, Install Base];

Concatenate (Data) Load * From [lib://xxxx/xxxx/xxx.csv] (txt, codepage is 28591, embedded labels, delimiter is ',', msq)

Final: CrossTable(FRMonth,FRPerc,2) LOAD * RESIDENT Data;

Drop table Data;

[rwunderlich]

@Vegar That's a very clever idea!

-Rob 

[MishaM]

Thank you, @rwunderlich!

I tweaked the load script to the following and used an if expression on the chart to exclude [Model Std] and [Install Base] on the x-axis:

[xxxxxx/xxxxxx/xxxxxxxxxxxxxxxxxxx]:
CrossTable(FRMonth,FRPerc,2)
LOAD
[Model Std],
[Install Base],
*

FROM [lib://xxxxxxx/xxxxx/xxxxxxxx/xxxxxxxxxx.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);