loading of multiple tables into qlik sense

[kishore3k]

I can able to bring sheet2,sheet3,sheet4 at the same time because they have same number of fields , but when i add sheet 1(which dont have recordtype) I could't able to because it always end up having error while loading ....

code I wrote in the script

For i = 1 to 4

Let vSheetName = pick( i,’Sheet1’,‘Sheet2',’Sheet3’,‘Sheet4’);

Let vFieldName = pick( i,’’ ,’skills’ ,’behaviour’,’class’);

Let vRecordType = pick( i,’marksonly’ ,’skills’ ,’behaviour’,’class’);            

CrossTable(test, markscored, 4)// marks1 and marks 2 under test & values in the marks unders markscored

LOAD

    firstname,

    lastname,

    '$(vRecordType)' as recordtype,

    $(vFieldName) AS recordvalue,

marks1,

marks2

from(………………………….((ooxml, embedded labels, header is 1 lines, table is $(vSheetName)))

Next

let i=Null();        

Message was edited by: Kishore Kumar

[dwforest · Accepted solution]

You'd have to load the 4th sheet outside the loop (without the missing field).

After loading it to a temp table, JOIN LOAD *, '' AS 'recordtype' RESIDNT temp;  DROP temp;

[kishore3k]

Hi David,

thanks for the reply.

so I should load sheet 1 separately first as temp table.

then join that table with other three tables .

then drop the table temp right?

[dwforest]

Because of the Crosstable, i'd load the 1st with the missing field using a hard-coded value.

You can follow that with the loop to load the other three, they will automatically concatenate as the fields match.

Use this temp table as a source for your crosstable and then drop it.