Solved: field not found & exists() function - Qlik Community

Report Inappropriate Content · ‎2017-09-12

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 · ‎2017-09-12

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.

View solution in original post

its_anandrjs · ‎2017-09-12

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

Report Inappropriate Content · ‎2017-09-12

hello,

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

[* Income Treatment]

regards,

Anonymous · ‎2017-09-12

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.

Report Inappropriate Content · ‎2017-09-12

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

Anonymous · ‎2017-09-12

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

Report Inappropriate Content · ‎2017-09-12

Hello,

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

Camille Menten

Anonymous · ‎2017-09-12

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

Anonymous · ‎2017-09-12

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.

Report Inappropriate Content · ‎2017-09-12

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