topic Re: Challenge! No one has the right answer do far! Skip missing field. in Water Cooler

Challenge! No one has the right answer do far! Skip missing field.

danm84306 — Thu, 21 Aug 2014 14:52:31 GMT

Hello,

I am looking for code for a seemingly simple task. Load multiple csv files and skip a field load if it does not exist. Example- I am loading fields [OptionA] [OptionB] [OptionC] and [OptionD]. In some files, [OptionD] is called [OptionE]. In others it may be [OptionF] [OptionG]. I do not want a load statement for each file since it will take too long. I want to load \\my location\*.csv to load all files. But I need to skip if a field does not exist. I cannot load all fields since I am dealing with 50+ columns and 75+ files. I simply need to load 4 fields but the 4^th field can have up to 7 names (they all mean the same thing). I am stuck and I don’t this this is possible in QlikView.

Re: Challenge! No one has the right answer do far! Skip missing field.

whiteline — Thu, 21 Aug 2014 15:02:09 GMT

Hi.

You could split the load process into two steps: check the fields and store column#, load if ok.

To check the fields you can load only the first string, transform it with crosstable tag and perform checks with applymap (for renaming) or something else. You should also store the column # of each field during transformation.

Then you could use that information to parameterize the load with $-sign expansion and skip the first row using column numbers.

Seems not so hard 😃

Re: Challenge! No one has the right answer do far! Skip missing field.

jonathandienst — Thu, 21 Aug 2014 15:17:37 GMT

I think this is what you are after:

T_Cols:

LOAD '[' & Concat(@1, '],[') & ']' As ColNames

FROM [Data.xlsx]

(ooxml, no labels, table is Sheet1, filters(Transpose()));

Let zCols = Peek('ColNames');

DROP Table T_Cols;

Data:

LOAD

$(zCols)

FROM [Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

The first transposed load gets the field names, and the second then loads those columns.

HTH

Jonathan

Re: Challenge! No one has the right answer do far! Skip missing field.

danm84306 — Thu, 21 Aug 2014 15:25:56 GMT

Neither option worked. The columns are not in the same set location. I need a simple skip if the filed name does not exist.

Re: Re: Challenge! No one has the right answer do far! Skip missing field.

rwunderlich — Thu, 21 Aug 2014 16:09:13 GMT

Here's an idea.

AllData:

LOAD filename() as Key, A, B, C

from ...;

SET ErrorMode=0; // Ignore Errors

Option:

LOAD filename() as Key, D as Option

from ...;

Option:

LOAD filename() as Key, E as Option // Continue with F, etc as needed

from ...;

LEFT JOIN (AllData) LOAD * RESIDENT Option;

DROP TABLE Option;

-Rob

Re: Challenge! No one has the right answer do far! Skip missing field.

danm84306 — Thu, 21 Aug 2014 16:30:34 GMT

Hello,

This option will not work. 75+ files with 7 possible field names. I am looking for a simple load all files in a folder and skip if file does not contain the field name.

Re: Challenge! No one has the right answer do far! Skip missing field.

danm84306 — Thu, 21 Aug 2014 16:52:46 GMT

I don't think what i am looking for is possible.

Re: Challenge! No one has the right answer do far! Skip missing field.

rwunderlich — Thu, 21 Aug 2014 16:57:00 GMT

I think perhaps I didn't make it clear that you use the wildcard in the from ...

\\my location\*.csv

Yes, you have to code one LOAD statement for each of the possible field names.

You said this "option will not work" and you said the same thing about Whiteline & Jonathan's answers as well, although they looked perfectly viable to me. I don't think you are going to find the "simple load" (like a magic option) but there are a number of viable solutions.

-Rob

Re: Challenge! No one has the right answer do far! Skip missing field.

jonathandienst — Fri, 22 Aug 2014 05:58:50 GMT

It would stop us all guessing if you posted a couple of your source data spreadsheets. Leave the headers intact, but remove most of the data rows, leaving about 10-20 in each.

Re: Re: Challenge! No one has the right answer do far! Skip missing field.

danm84306 — Fri, 22 Aug 2014 15:51:51 GMT

Great Idea! Here is the setup, Test files are in a folder. I would like to import only these fields

record_status

chain_n

Location

*stunum*

disposition

Campaign

but stunum can be stunum, Stunum,StuNum,ID,Id,id, and Print_Id. Only one of this form will exist in a file. Also there are not 7 files, about 30 files in 20+ folders each. I would like to load all files in a folder with only the fields mentioned.

Re: Challenge! No one has the right answer do far! Skip missing field.

Fri, 22 Aug 2014 16:41:01 GMT

HI,

How about loading excel files, then store all records to a text file.

And then reload the stored files into QV.

I don't know this is correct answer.

I hope this is helpful.

Step 1)

Tmp1:

load *

FROM

(ooxml, embedded labels);

Store

record_status,

chain_n,

Location,

disposition,

Campaign,

Stunum

From Tmp1 into test1-7.txt(txt);

Step 2)

Test7:

LOAD record_status,

chain_n,

Location,

disposition,

Campaign,

Stunum

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

WanKI,

Re: Challenge! No one has the right answer do far! Skip missing field.

danm84306 — Fri, 22 Aug 2014 16:45:14 GMT

Hello, This option may work on a smaller level but I am facing 600+csv files. This would be too much to do for the set up am looking for.

Re: Re: Challenge! No one has the right answer do far! Skip missing field.

rwunderlich — Fri, 22 Aug 2014 19:55:40 GMT

DataAll:

LOAD

Filepath() as Key,

record_status,

call_result,

call_time,

chain_n,

Location,

disposition,

Campaign

FROM

[*.csv]

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

SUB LoadField(field)

SET ErrorMode=0;

AddField:

LOAD

Filepath() as Key,

[$(field)] as stunum

FROM

[*.csv]

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

SET ErrorMode=1;

ENDSUB

FOR EACH field IN 'stunum','Stunum','StuNum','ID','Id','id','Print_Id'

CALL LoadField('$(field)')

I know, I know, this won't work either

-Rob

Re: Challenge! No one has the right answer do far! Skip missing field.

danm84306 — Fri, 22 Aug 2014 20:26:23 GMT

Hello Rob,

This is the closest solution so far! I am having trouble with my expressions when I try to count distinct on the stunum but I should get it with some tweaking. Thank you very much for your help!!

Re: Challenge! No one has the right answer do far! Skip missing field.

rwunderlich — Fri, 22 Aug 2014 21:05:05 GMT

Your'e welcome. Glad we got you something workable,

-Rob

http://masterssummit.com

http://robwunderlich.com