CODEWITHSUNDEEP
qlikview
ANAS89
ANAS89

Reputation: 59

Load multiple Excel sheets using For loop with QlikView

I want to load data from two different Excel files, and use them in the same table in QlikView.

I have two files (DIVISION.xls and REGION.xls), and I'm using the following code:

let tt = 'DIVISION$' and 'REGION$';

FOR Each db_schema in 'DIVISION.xls','REGION.xls'
    FOR Each v_db in $(tt)
        div_reg_table:
        LOAD *
        FROM $(db_schema) 
        (biff, embedded labels, table is $(v_db));
    NEXT
NEXT

This code works fine, and does not show any error, but I don't get any data, and I don't see my new table (div_reg_table).

Can you help me?

Upvotes: 0

Views: 2833

Answers (2)

i_saw_drones
i_saw_drones

Reputation: 3506

The main reason that your code does not load any data is due to the form of the tt variable.

When your inner loop executes, it evaluates tt (denoted by $(tt)), which then results in the evaluation of:

'DIVISION$' AND 'REGION$'

Which results in null, since these are just two strings.

If you change your statement slightly, from LET to SET and remove the AND, then the inner loop will work. For example:

SET tt = 'DIVISION$', 'REGION$';

However, this also now means that your inner loop will be executed for each value in tt for both workbooks. This means that unless you have a REGION sheet in your DIVISION workbook, then the load will fail.

To avoid this, you may wish to restructure your script slightly so that you have a control table which details the files and tables to load. An example I prepared is shown below:

FileList:
LOAD * INLINE [
    FileName, TableName
    REGION.XLS, REGION$
    DIVISION.XLS, DIVISION$
];


FOR i = 0 TO NoOfRows('FileList') - 1

    LET FileName = peek('FileName', i, 'FileList'); 
    LET TableName = peek('TableName', i, 'FileList'); 

    div_reg_table:
    LOAD
        *
    FROM '$(FileName)'
    (biff, embedded labels, table is '$(TableName)');

NEXT

If you have just two files DIVISION.XLS and REGION.XLS then it may be worth just using two separate loads one after the other, and removing the for loop entirely. For example:

div_reg_table:
LOAD
    *
FROM [DIVISION.XLS]
(biff, embedded labels, table is DIVISION$)
WHERE DivisionName = 'AA';

LOAD
    *
FROM [REGION.XLS]
(biff, embedded labels, table is REGION$)
WHERE RegionName = 'BB';

Upvotes: 3

The Budac
The Budac

Reputation: 1633

Don't you need to make the noofrows('Filelist') test be 1 less than the answer.

noofrows('filelist') will evaluate to 2 so you will get a loop step for 0,1 and 2. So 2 will return a null which will cause it to fail.

I did it like this:

FileList: LOAD * INLINE [ FileName, TableName REGION.XLS, REGION DIVISION.XLS, DIVISION ];

let vNo= NoOfRows('FileList')-1;

FOR i = 0 TO $(vNo)

LET FileName = peek('FileName', i, 'FileList'); 
LET TableName = peek('TableName', i, 'FileList'); 

div_reg_table:
LOAD
    *,
    $(i) as I,
    FileBaseName() as SOURCE
FROM '$(FileName)'
(biff, embedded labels, table is '$(TableName)');

NEXT

Upvotes: 0

Related Questions