Selecting New Columns only from excel

thepope — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Selecting New Columns only from excel

prieper — Mon, 21 Jan 2019 16:32:01 GMT

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.

Re: Selecting New Columns only from excel

thepope — Wed, 23 Jan 2019 09:51:52 GMT

Thanks,

Works a treat

topic Re: Selecting New Columns only from excel in QlikView

Selecting New Columns only from excel

Re: Selecting New Columns only from excel

Re: Selecting New Columns only from excel