Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Excelfiles with different number of fields

Hi,

i'm trying to load a number of excelfiles into qlikview. My problem is that not all files has exactly the same fields,

i.e. not all fields exists in all files. 

Is there a way check if a field exists or not in the load-function?

For example:
File1 has fields: A,B,C,D,E

File2 has fields: A,C,D

File2 is missing fields B and D. 

There's about 25 files and sometimes it ca be up to 10 fields missing, which makes changing the data a bit complicated

4 Replies
Not applicable

Re: Excelfiles with different number of fields

Not applicable

Re: Excelfiles with different number of fields

Is these files are in the same excel file (work books) or different excel files ?

All these excel files are in same folder , just create the for loop and concatenate all the excel files into one table.

Not applicable

Re: Excelfiles with different number of fields

Short answer: Different excel files

I'm starting to think I have two problems...

I have about 50 excel files, from each file I'm loading 3 tables (in this case called t1,t2 and t3).

t1 has fields: ID, Date, Time, Place, Weather

t2 has fields: ID, Date, Time, Organisation,

t3 has fields: ID, Date, Time, Place, Member1, Member2....Member10

Problem 1: Not all memberfields exits in all t3. Hence the need to check if a field exits or not.

                    I had an idea to solve that problem by using * to load all fields.
That lead to problem no2: More than one field has the same name => synthetic keys.
               I tried to solve that problem by using Qualify, so that  date would be called t3.Date.
               the problem with this solution is that Qualify adds a table number each time it reads a table

               t3-1.Date
               t3-2.Date

               ...

               t3-50.Date

rustyfishbones
Honored Contributor II

Re: Excelfiles with different number of fields

You can Concatenate each excel file

it would look like this before the LOAD staement for each table you want to Concat with the main table, or whatever your main table is called

2013-11-26_1624.png

Community Browser