Multiple distinct arrays on the same excel sheet

Anonymous — Wed, 25 Oct 2017 15:13:24 GMT

Hi everybody.

I've got an exel file to load with multiple arrays on a single sheet.

So, i would like to know if there is a solution to load each array distincly.

For exemple, Ive got this ones:

I'd like to get:

tab1.firstRow = 1, tab1.lastRow = 5

tab2.firstRow = 8, tab2.lastRow = 10

(Or maybe tab2.firstRow = 6, tab2.lastRow = 8 because LOAD statement don't take the blank row)

So, I'll be able to do :

PERSON:

LET tab1.nRecords = $(tab1.lastRow) - $(tab1.firstRow);

LET tab1.startRow = $(tab1.firstRow) - 1

FIRST tab1.nRecords

LOAD *

FROM [excel_file.xlsx]

(ooxml, embedded labels, header is tab1.startRow lines, table is sheet1);

SOCIETY:

LET tab2.nRecords = $(tab2.lastRow) - $(tab2.firstRow);

LET tab2.startRow = $(tab2.firstRow) - 1

FIRST tab2.nRecords

LOAD *

FROM [excel_file.xlsx]

(ooxml, embedded labels, header is tab1.startRow lines, table is sheet1);

Or maybye another tip exists?

Thanks for the help !

Re: Multiple distinct arrays on the same excel sheet

Anonymous — Tue, 31 Oct 2017 08:46:20 GMT

Hi.

Since I've posted my question, I've worked on.

So, you can find a proposition:

// 1 - Get the start position of each array

ARRAY_STRUCTURE:

LOAD RecNo() as position, RowNo() as id, A as arrayName

FROM [excel_file.xlsx]

(ooxml, table is sheet1)

WHERE A = 'lastname' OR A = 'society';

// 2 - Create some empty tables in order to receive the data

For Each vArrayName in FieldValueList('ARRAY_STRUCTURE.arrayName')

DATA_$(vArrayName):

LOAD $(vArrayName) as arrayName

AutoGenerate(0);

Next vArrayName ;

// 3 - Get data from each array (concatenate with each empty table)

FOR Each nId in FieldValueList('ARRAY_STRUCTURE.id')

LET vArrayPosition = FieldValue('ARRAY_STRUCTURE.position', nId);

LET vArrayName = FieldValue('ARRAY_STRUCTURE.position', arrayName);

LET vHeaderLine = vArrayPosition -1;

LET vEndLine = FieldValue('$(pArrayStructure).position', nId + 1) - vArrayPosition - 1;

If(Not IsNull(vEndLine)) Then

// Get data, til the next array

Concatenate(DATA_$(vArrayName))

FIRST $(vEndLine)

LOAD $(nId) as tabIndex, $(vArrayName) as arrayName, *

FROM [excel_file.xlsx]

(ooxml, embedded labels, header is $(vHeaderLine) lines, table is sheet1);

Else

// Last array: get all the remaining data

Concatenate(DATA_$(vArrayName))

LOAD $(nId) as tabIndex, $(vArrayName) as arrayName, *

FROM [excel_file.xlsx]

(ooxml, embedded labels, header is $(vHeaderLine) lines, table is sheet1);

EndIf;

NEXT nId ;

In order to use this method in the future, I've put each of this step in a procedure, with flexable parameters.

topic Re: Multiple distinct arrays on the same excel sheet in QlikView

Multiple distinct arrays on the same excel sheet

Re: Multiple distinct arrays on the same excel sheet