11 Replies Latest reply: Sep 12, 2017 9:08 AM by Camille Menten RSS

    field not found  & exists() function

    Camille Menten

      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)

                   ;

        • Re: field not found  & exists() function
          Anand Chouhan

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

            • Re: field not found  & exists() function
              Camille Menten

              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
                  Bill Markham

                  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.

                    • Re: field not found  & exists() function
                      Camille Menten

                      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
                          Bill Markham

                          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

                            • Re: field not found  & exists() function
                              Camille Menten

                              Hello,

                               

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

                               

                               

                               

                              Camille Menten

                                • Re: field not found  & exists() function
                                  Bill Markham

                                  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
                                      Bill Markham

                                      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.

                                      • Re: field not found  & exists() function
                                        Camille Menten

                                        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

                                          • Re: field not found  & exists() function
                                            Bill Markham

                                            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.