Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SerSwagster
Creator
Creator

Loading multiple files having same structure (except for a column)

Hi everyone,

I'm loading multiple files of a folder with this method:

 

For each vFileName in Filelist ('hello_*.xlsx')

LOAD

id,

name,

address,

.

.

.

Next vFileName

 

These files have the same structure, except for one column, "phone_number": some files have this column, others not. If I put phone_number as a loaded column, I have an error. Since I can't modify source files and add phone_number column to those missing, How can i behave? Is there a syntax like: If(filename = 'hello_1', phone_number, null)?

My goal is to load phone_number column if it is already in the file, otherwise to create the phone_number column and set it to a default value for files missing it.

Thank you in advance.

2 Solutions

Accepted Solutions
Or
MVP
MVP

Set ErrorMode = 0;

For each vFileName in Filelist ('hello_*.xlsx')

User:

LOAD

id,

name,

address,

phone_number

FROM
[$(vFileName)]

(ooxml, embedded labels, table is [Sheet 1]);

if ScriptError > 0 THEN

User:

LOAD

id,

name,

address,

'555-555-5555' as phone_number

FROM
[$(vFileName)]

(ooxml, embedded labels, table is [Sheet 1]);

End If

 


Next vFileName

Set ErrorMode = 1;

View solution in original post

mikaelsc
Specialist
Specialist

A:
LOAD * INLINE [

id, name, address, phone_number

];

 

For each vFileName in Filelist ('hello_*.xlsx')

concatenate(A)

LOAD

*

FROM
[$(vFileName)]

(ooxml, embedded labels, table is [Sheet 1]);

 

A_Reworked: 

load 

alt(phone_number,'555-555-...')  as FinalPhoneNo,

id,

name,

address

resident A; 

drop table A;

View solution in original post

13 Replies
mikaelsc
Specialist
Specialist

create an empty table with an inline load containing the columns you want, and do a concatenate load of all your tables with it. 

Or
MVP
MVP

There may be a clean solution for this, but I can't think of it, so I'll suggest this rather messy approach which uses ErrorMode 0 and tests whether an error occurred reading the file, in which case it goes into a second load statement that doesn't have the phone number field in it (successful reading of that one will reset the ScriptError variable to 0). I think you could just test on ScriptError = 11, but you need to double check that with the actual error you get.

Note that if you encounter any error except the missing phone number field, this won't know how to deal with that properly.

Set Errormode = 0;

For each vFileName in Filelist ('hello_*.xlsx')

LOAD // This is the version with the phone number

id,

name,

address,

.

.

.

if ScriptError > 0 THEN

Load stuff  // This is the version without the phone number in the file, so you just don't read that column here

End if

Next vFileName

Set ErrorMode = 1;

SerSwagster
Creator
Creator
Author

Can you write me the right code of your solution, please? So I can understand it better.

This is the original code:

For each vFileName in Filelist ('hello_*.xlsx')

User:

LOAD

id,

name,

address,

phone_number

FROM
[$(vFileName)]

(ooxml, embedded labels, table is [Sheet 1]);
Next vFileName

Or
MVP
MVP

Set ErrorMode = 0;

For each vFileName in Filelist ('hello_*.xlsx')

User:

LOAD

id,

name,

address,

phone_number

FROM
[$(vFileName)]

(ooxml, embedded labels, table is [Sheet 1]);

if ScriptError > 0 THEN

User:

LOAD

id,

name,

address,

'555-555-5555' as phone_number

FROM
[$(vFileName)]

(ooxml, embedded labels, table is [Sheet 1]);

End If

 


Next vFileName

Set ErrorMode = 1;

SerSwagster
Creator
Creator
Author

Sorry for another message. How should I modify this code if I want to use the Error Mode only for phone_number field? In short, I want that the other fields are not impacted by Error Mode. In the code, the other fields should be loaded normally, and only phone_number with Error Mode. Thanks in advance, and sorry for the extra inconvenience.

Or
MVP
MVP

You can't. ErrorMode is a global variable. You could look at using ErrorList to figure out what exactly went wrong and branching out your conditions accordingly, but that's about it for your options if you're using this method.

mikaelsc
Specialist
Specialist

give another chance to what i suggested: 

create an empty table with an inline load listing all relevant fieldnames; 

concatenate all tables you load with the created empty table (load * from ... *xlsx) 

 

then do the needed transformations (alt/coalesce phone number,defaultvalue), ...  in a new table using a resident load from the previous one; 

 

Or
MVP
MVP


@mikaelsc wrote:

create an empty table with an inline load listing all relevant fieldnames; 

concatenate all tables you load with the created empty table (load * from ... *xlsx) 

 


This sounds like an interesting approach, but could you explain how this will help get around the fact that the list of files to be read is dynamic and each file might have different columns? If your suggestion has a solution for that I'd love to hear what it is, as this is something I've had to deal with in the past and the ErrorMode approach is clunky.

mikaelsc
Specialist
Specialist

if the fieldnames are different, it doesn't work indeed. 

but if fields are available, they either will be available in the "list" defined in the first (structure) table; either they will "just" be added to the existing table (and be empty when files don't contain certain fields)