field not found & exists() function

[#1]

hello,

I have a script where I want to load a field (field 1) from different CSV files.

The field exists in some of the files, but not all of them.

For the fields where [* Income Treatment]  does not exist, I want the script to create a new [Income Treatment]

For the fields where  [* Income Treatment] does exist, I want the script to load it as [Income Treatment]

this in order to have at the end 1 field "field 2" with the concatenated fields.

I tried to use error_mode = 0; & error_mode = 1; but it doesn't work.

Then I tried to use the function exists() but still it doesn't work.

Can you help to order my script to make it work? Thanks a lot!

FYI below original part of script

[Investment Universe]:

        LOAD

            Num(PurgeChar([Year Month], '-')) as [%YearMonthID],

            [ISIN]        as [%IsinID],

            Null()        as [Internal Fund Type],

            1          as [IsInUniverse_],

            'External'       as [Fund Type],

            'In'        as [Investment Universe],

        FROM

            [$(vL.ExternalFundFile)]

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

        Where

            Len(Trim([ISIN])) > 0

            and

            Num(PurgeChar([Year Month], '-')) >= $(vL.FirstDateToLoad_Metrix)

             ;

[Anonymous · Accepted solution]

When you are trying to create the [Investment Universe Final] table the 2 loads don't define where the data is coming from, they probably both need a line like ;

resident [Investment Universe]

But having said there could be an even easier way to do it, here is a basic example.  I am guessing that with your [$(vL.ExternalFundFile)] you are actually doing a wild card load

//  First autogenerate a table with 0 rows, that defines 2 fields, Field1 & Field 2

TableOne :

Load

    Null()  as  Field1 ,

    Null()  as  Field2

autogenerate 0

;

//  Then simply concatenate the wild card load * onto this table

Concatenate ( TableOne )

LOAD

    *

FROM [lib://Bill/*.csv]

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

//Because both Field1 & Field2 have been pre-created if a field is missing from a source csv it will be filled with Null's.

I used 2 basic csv files:

File1.csv containing:

Field1, Field2

A,1

B,2

C,3

File2.csv containing:

Field1

X

Y

Z

And it worked.

Try giving this simple example a go first with a couple of test csv's like above and if it works fine for you, then you can convert it to use your real data and transformations.

[#3]

thank you so much! i finally see the light at the end of the tunnel! it does seem to work like this!!

now fine tuning.