topic Re: Load multiple files with some missing some colums in QlikView

Load multiple files with some missing some colums

Anonymous — Fri, 18 Sep 2015 15:37:08 GMT

I don' t seem to find the answer to this.

I want to load multiple CSV files, in which the columns are named, but some of them miss some columns

Ex.

file 1 to n have columns (a,b,c,d)

file n+1 to p have columns (a,b,c,d,e)

if my load scripts tries to read column 'e' I get an error for files 1 to n.

How can I prevent this?

Thanks in advance

Re: Load multiple files with some missing some colums

sudeepkm — Fri, 18 Sep 2015 15:38:49 GMT

you can take a look at here. A file checker utility for file based data sources

Re: Load multiple files with some missing some colums

Anonymous — Fri, 18 Sep 2015 15:48:02 GMT

Thank you, I get your idea, but this seems an overkill for my case.

Is there really no way in Qlikview load scripts to do something like this

LOAD

a as [field a],

b as [field b],

c as [field c],

d as [field d],

if (e exists, e, null) as [field d]

...

Re: Load multiple files with some missing some colums

sudeepkm — Fri, 18 Sep 2015 16:00:29 GMT

I think load * from files can work. try it.

Re: Load multiple files with some missing some colums

maniram23 — Fri, 18 Sep 2015 16:36:55 GMT

Try this one . Its helpful for you.

let vs='File n+1';

FOR Each vs in 'Fil 1','File n+1';

LOAD *

FROM

(biff, embedded labels, table is $(vs));

NEXT vs;

Re: Load multiple files with some missing some colums

maxgro — Fri, 18 Sep 2015 16:52:23 GMT

set concat='';

for Each file in FileList('*.csv')

table:

$(concat)

load *, '$(file)' as filename

from $(file);

set concat=' concatenate (table) ';

NEXT;

Re: Load multiple files with some missing some colums

marcus_sommer — Fri, 18 Sep 2015 18:55:25 GMT

There is no way inside from a load to check if fields are existing or not. The easiest way would be the suggestions below from maxgro‌- use it, it's easy.

- Marcus

Re: Load multiple files with some missing some colums

qlikviewwizard — Sat, 19 Sep 2015 00:46:34 GMT

‌it is not possible .

Re: Load multiple files with some missing some colums

Anonymous — Mon, 21 Sep 2015 07:51:17 GMT

This is probably it.

Re: Load multiple files with some missing some colums

marcus_sommer — Mon, 21 Sep 2015 08:53:20 GMT

Have you checked the solution which had maxgro suggested above?

Re: Load multiple files with some missing some colums

simenkg — Mon, 21 Sep 2015 09:05:21 GMT

Table:

LOAD * FROM

[.\Source\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

QlikView should do an automatic concatenate and the missing fields will be populated with Nulls().

If none of the files contain field e, but you use field e later in the script, then you could do something like this:

Table:
Load null() as a,

null() as b,

null() as c,

null() as d,

null() as e

autogenerate 0;

concatenate(Table)

LOAD * FROM

[.\Source\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Re: Load multiple files with some missing some colums

Anonymous — Mon, 21 Sep 2015 11:18:55 GMT

OK this allows me to load everthing correctly in the table "table", but as I do some transformation at load time, I should do that in two steps I guess like this :

//First solution provided by maxgro to load in temp table

set concat='';

for Each file in FileList('*.csv')

table:

$(concat)

load *, '$(file)' as filename

from $(file);

set concat=' concatenate (table) ';

NEXT;

//Then do my transformations

noconcatenate

realtable:

load

a as [field a],

b as [field b],

(if c=1, 'OK', 'KO') as [field c], //stupid transformation example

d as [field d],

if(isnull(e), 'No field e', e) as [field e] //Works now

resident table;

drop table table;

Re: Load multiple files with some missing some colums

Anonymous — Mon, 21 Sep 2015 11:24:49 GMT

Implemented maxgro solution (see answer above) and it works.

qlikviewwizard is also right though, as you point it "There is no way inside from a load to check if fields are existing or not"