Qlik Community

Ask a Question

QlikView Documents

QlikView documentation and resources.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY

Generate columns dynamically without changes

kamakshisuram
Creator
Creator

Generate columns dynamically without changes

Hello team.

One of the user asked that newly added columns in source should automatically populate in Qlikview work book without any changes to the work book.

I tried one POC for this case and implemented the same. Please find it in below attachment.

Note: This will not be feasible if we have more columns/rows in the table.

Tags (2)
Labels (1)
Attachments
Comments
maleksafa
Specialist
Specialist

can you explain more the use case behind it? why do you need to do this and not simply Load * ?

bbmmouha
Creator
Creator

me too i didn't guess the topic

0 Likes
preminqlik
Specialist II
Specialist II

very nice ...add the below line to work on all selections

PK as key,

PK Resident Table;'                          

0 Likes
robert_mika

Could you add the load script code?

Most of the ushave PE version.

0 Likes
kamakshisuram
Creator
Creator

Hi Robert,

Please find it below.

set cmd1 ='';

set Cmd2='';

Set LoadCmd='';

Table:

LOAD *

FROM

(ooxml, embedded labels);

FOR fieldNo = 2 to NoOfFields('Table')

           let _fieldName = fieldName($(fieldNo), 'Table');

                    

           TABLE_FIELDS:

           LOAD

                           '$(_fieldName)' as FIELD_NAME

                          

                             autogenerate 1;

                                                  

                           Let cmd1='$(cmd1)' &' Load ' & chr(39) &'$(_fieldName)' &chr(39)  & ' as colname ,' & '$(_fieldName)' &' as data ,PK as key Resident Table;'                          

                           & if($(fieldNo)= NoOfFields('Table'),'','Concatenate(Test1)');

             let LoadCmd=' Test1:' & '$(cmd1)' ;

                  

                    

          

          

          

       next

Load * Resident TABLE_FIELDS;

$(LoadCmd);

DynamicColumnsQVWScreen.JPG

0 Likes
kamakshisuram
Creator
Creator

Hi,

Consider we have 3 columns in the source and these 3 columns are displaying in QVW as a table.

For suppose User adds 4 th column in the source, this should automatically add into QVW table without any modifications to existing workbook.

I achieved this with the help of Cross table and Dynamically generated Load scrip.

My Source is having PK column as Primary Key. Initially I am fetching the number of columns in the source and looping to each column data and generating Load script. This script will store in Loadcmd variable and which will be called after the loop. Please find it below . and let me know in case of any questions.

set cmd1 ='';

set Cmd2='';

Set LoadCmd='';

Table:

LOAD *

FROM

(ooxml, embedded labels);

FOR fieldNo = 2 to NoOfFields('Table')

           let _fieldName = fieldName($(fieldNo), 'Table');

                    

           TABLE_FIELDS:

           LOAD

                           '$(_fieldName)' as FIELD_NAME

                          

                             autogenerate 1;

                                                  

                           Let cmd1='$(cmd1)' &' Load ' & chr(39) &'$(_fieldName)' &chr(39)  & ' as colname ,' & '$(_fieldName)' &' as data ,PK as key Resident Table;'                          

                           & if($(fieldNo)= NoOfFields('Table'),'','Concatenate(Test1)');

             let LoadCmd=' Test1:' & '$(cmd1)' ;

                  

                    

          

          

          

       next

Load * Resident TABLE_FIELDS;

$(LoadCmd);

0 Likes
Not applicable

Awesome Explanation..

0 Likes
mithilesh17
Partner
Partner

awesome post..thanks!

0 Likes
manideep78
Partner
Partner

I know this is very old post,  But how do I do it in straight table. also the columns should be used as filters

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-03-27 05:40 AM
Updated by: