Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Selecting New Columns only from excel

Hi All,

I have an excel file where periodic data is added as new columns, I would like to just import the new data (and export to a qvd) so that I can then trend the data

Please see the attached file as an example.

There will always be a fixed number of rows but the column count will increase per period by 5

any help would be appreciated

2 Solutions

Accepted Solutions
Highlighted
Master II
Master II

Think that you need a twofold approach:
First is just to load the second line and determine, how many columns are available and to get then the lastmost columns.
aircode might be something like:

Period:
FIRST 1 LOAD
    *
FROM
[QV Template.xlsx]
(ooxml, no labels, header is 0 lines, table is Sheet1);

LET sPeriod = PEEK(FIELDNAME($(iColumns) - 4, 'Period'), 0, 'Period');

DROP TABLE Period;


Header:
FIRST 1 LOAD
    *
FROM
[QV Template.xlsx]
(ooxml, no labels, header is 1 lines, table is Sheet1);

LET iColumns = NOOFFIELDS('Header');

LET Col1 = FIELDNAME($(iColumns) - 4, 'Header');
LET Col2 = FIELDNAME($(iColumns) - 3, 'Header');
// etc

DROP TABLE Header;

Data:
LOAD
    A                            AS Data,
    $(Col1)                        AS Col1,    // choose yr name
    $(Col2)                        AS Col2
FROM
[QV Template.xlsx]
(ooxml, No labels, header is 1 lines, table is Sheet1)
;    

STORE Data INTO [Data$(sPeriod).QVD] (QVD);

HTH
Peter

edit: code completed for adding Period and store as QVD.

View solution in original post

Highlighted
Contributor II
Contributor II

Thanks,

 

Works a treatSmiley Happy

View solution in original post

2 Replies
Highlighted
Master II
Master II

Think that you need a twofold approach:
First is just to load the second line and determine, how many columns are available and to get then the lastmost columns.
aircode might be something like:

Period:
FIRST 1 LOAD
    *
FROM
[QV Template.xlsx]
(ooxml, no labels, header is 0 lines, table is Sheet1);

LET sPeriod = PEEK(FIELDNAME($(iColumns) - 4, 'Period'), 0, 'Period');

DROP TABLE Period;


Header:
FIRST 1 LOAD
    *
FROM
[QV Template.xlsx]
(ooxml, no labels, header is 1 lines, table is Sheet1);

LET iColumns = NOOFFIELDS('Header');

LET Col1 = FIELDNAME($(iColumns) - 4, 'Header');
LET Col2 = FIELDNAME($(iColumns) - 3, 'Header');
// etc

DROP TABLE Header;

Data:
LOAD
    A                            AS Data,
    $(Col1)                        AS Col1,    // choose yr name
    $(Col2)                        AS Col2
FROM
[QV Template.xlsx]
(ooxml, No labels, header is 1 lines, table is Sheet1)
;    

STORE Data INTO [Data$(sPeriod).QVD] (QVD);

HTH
Peter

edit: code completed for adding Period and store as QVD.

View solution in original post

Highlighted
Contributor II
Contributor II

Thanks,

 

Works a treatSmiley Happy

View solution in original post