New to Qlik Sense

[SerSwagster]

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.

[mikaelsc]

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]

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]

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 · Accepted solution]

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]

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]

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]

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]

---

@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]

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)