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

[danm84306]

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.

[#2]

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,

[danm84306]

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.

[rwunderlich · Accepted solution]

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)')

NEXT

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

-Rob

[danm84306]

  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!!

[rwunderlich]

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

-Rob

http://masterssummit.com

http://robwunderlich.com