Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

field not found & exists() function

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)

             ;

1 Solution

Accepted Solutions

Re: field not found & exists() function

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.

11 Replies

Re: field not found & exists() function

In your variable vL.FirstDateToLoad_Metrix what type of data is num or text or you can try below

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

Not applicable

Re: field not found  & exists() function

hello,

no this doesn't change anything. Fact is that he still doesn't find field

[* Income Treatment]  

regards,

Re: field not found & exists() function

Did you say earlier that the field does not exist in some of your csv files ?

That would explain why it can't find it, if it does not always exist.

Not applicable

Re: field not found  & exists() function

yes indeed. in some files the field does not exist.

when it does not exist, I want to create the field with specific value in it: 'N/A'

when it does exist, I want to load it with it the data from the csv

Re: field not found & exists() function

Have a look at this thread for inspiration - It is an old QlikView thread, but Qlik scripting has not changed for years so the thread is still valid.

How to check if field (column) exists

Not applicable

Re: field not found & exists() function

Hello,

Yes i’ve read that too. But it’s not 100% clear to me

Camille Menten

Re: field not found & exists() function

Basically it is saying to first do a Load * to get all the fields that exist and then use the FieldNumber() function to see if the field in question exists or not and use an if statement to process for exists / not exists as per your needs

Re: field not found & exists() function

If you get stuck on a particular bit then feel to post your qvf and a couple of sample csv's, with & without the field.

Not applicable

Re: field not found & exists() function

OK, thanks for this explanation. Makes it more clear. However: am I obliged to use an Inline? Don't see the added value for me.

I'm trying to add the fieldnr into my script as below. I don't get any error message anymore, however my data is not loaded (is not going to table [Investment Universe Final]:

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

[Investment Universe Final]:

Load * ;

    LET X = FieldNumber( [* Income Treatment],[Investment Universe]);
    if $(X) = 0 then
      TRACE [* Income Treatment] ;
      Load
      'NA' as [Income Treatment];

    else
      Load
      [* Income Treatment] as [Income Treatment];

    end if

Community Browser