7 Replies Latest reply: Oct 18, 2016 6:31 PM by Marco Wedel RSS

    field not found

    krishna m

      hi,

      in single column of excel i have data of different fields (Year Country DirectTax IndirectTax OtherTax). i using SubField function to extract only Year and Country field value. below script executed successfully with Error: Field <> not found

       

      please give solution

       

      attached screenshot of sample data

       

      tableA:

      LOAD [Year Country Direct Tax Indirect Tax Other Tax] As Tax

      FROM

      [Practicing Data Files\Data for func Sub Field.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      tableB:

      LOAD SubField(Tax," ",1) As TaxYear,

        SubField(Tax," ",2) As TaxCountry

      Resident tableA;

       

      DROP Table tableA;

        • Re: field not found
          Marco Wedel

          Hi,

           

          your second subfield parameter " " is interpreted as field name because of the double quotes you used.

          Try single quotes instead to define a string as subfield delimiter:

           

          tableB:

          LOAD SubField(Tax,' ',1) As TaxYear,

            SubField(Tax,' ',2) As TaxCountry

          Resident tableA;

           

           

          hope this helps

           

          regards

           

          Marco

          • Re: field not found
            Marco Wedel

            given your sample data, you probably should load the subfields like this:

             

            QlikCommunity_Thread_236396_Pic1.JPG

             

            tableA:
            LOAD [Year Country Direct Tax Indirect Tax Other Tax] as Tax
            Inline [
            Year Country Direct Tax Indirect Tax Other Tax
            2011 Australia 23453 34509 93845
            2012; India 23453 34509 93845
            2013. USA 23453 34509 93845
            2014-- Russia 23453 34509 93845
            2015* Japan 23453 34509 93845
            2016 South Korea 23453 34509 93845
            ];
            
            tableB:  
            LOAD Left(Tax,4) as TaxYear,  
                 Trim(Left(Mid(Tax,Index(Tax,' ')),FindOneOf(Mid(Tax,Index(Tax,' ')),'0123456789')-1)) as TaxCountry  
            Resident tableA; 
            
            DROP Table tableA;
            

             

             

            hope this helps

             

            regards

             

            Marco

              • Re: field not found
                krishna m

                Script executed successfully but

                 

                1. you have used Inline Table data

                2. shall i know what this formula do

                     Trim(Left(Mid(Tax,Index(Tax,' ')),FindOneOf(Mid(Tax,Index(Tax,' ')),'0123456789')-1)) as TaxCountry

                 

                i want data from file instead of Inline table data and i want to remove extra characters after year like ' ; ', ' * ', ' / '

                 

                please provide simple formula to remove those extra

                  • Re: field not found
                    Marco Wedel

                    Hi,

                     

                    you can replace the Inline[] part with any other data source, e.g. files (actually I would have used your sample file if it had not been a word document enclosed image).

                     

                    The suggested expression extracts the substring between the first blank and the first subsequent numerical character and removes leading and trailing blanks, so I guess it should deliver the country part of your source strings (at least for the samples you provided).

                    You could also use PurgeChar() to remove specific characters, but I think that's only part of an alternative expression you are looking for.

                     

                    hope this helps

                     

                    regards

                     

                    Marco

                      • Re: field not found
                        krishna m

                        thanks Marco, PURGECHAR works

                         

                        1. attached excel file with data

                        2. below is the script

                        ISSUE: South Korea displaying as South in column Country and Korea in column Direct Tax

                        if i use SubField(tableA.Tax,'',2) As Country, country column display - values. PURGECHAR can't be used.

                         

                        please let me know the formula to display South Korea in column Country instead error mentioned in ISSUE

                         

                        below is the script

                        QUALIFY *;

                         

                        tableA:

                        LOAD Purgechar(Purgechar(Purgechar(Purgechar(Purgechar(Purgechar([Year Country DirectTax IndirectTax OtherTax],';'),'.'),'--'),'*'),'/'),':') As Tax

                        FROM

                        [Practicing Data Files\Data for func Sub Field.xlsx]

                        (ooxml, embedded labels, table is Sheet1);

                         

                        tableB:

                        LOAD SubField(tableA.Tax,' ',1) As Year,

                          SubField(tableA.Tax,' ',2) As Country,

                          SubField(tableA.Tax,' ',3) As DirectTax,

                          SubField(tableA.Tax,' ',4) As IndirectTax,

                          SubField(tableA.Tax,' ',5) As OtherTax

                        Resident tableA;

                         

                        DROP Table tableA;

                         

                        UNQUALIFY *;

                          • Re: field not found
                            Jonathan Dienst

                            You dont need to next PurgeChar() like that. This will be sufficient:

                            Purgechar(Purgechar[Year Country DirectTax IndirectTax OtherTax],';,.*/:-')

                             

                            or if you want to remove double hyphens and leave single hyphens intact:

                            Replace(Purgechar(Purgechar[Year Country DirectTax IndirectTax OtherTax],';,.*/:'), '--', '')

                            • Re: field not found
                              Marco Wedel

                              Hi,

                               

                              you also could use KeepChar instead of PurgeChar like this:

                               

                              QlikCommunity_Thread_236396_Pic2.JPG

                               

                              QUALIFY *;
                              
                              tableA:
                              LOAD *, KeepChar([Year Country DirectTax IndirectTax OtherTax],'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ') as Tax
                              FROM [Data for func Sub Field.xlsx] (ooxml, embedded labels, table is Sheet1);
                              
                              tableB:
                              LOAD SubField(tableA.Tax,' ',1) As Year,
                                Mid(tableA.Tax,Index(tableA.Tax,' ')+1,Index(tableA.Tax,' ',-3)-Index(tableA.Tax,' ')-1) As Country,
                                SubField(tableA.Tax,' ',-3) As DirectTax,
                                SubField(tableA.Tax,' ',-2) As IndirectTax,
                                SubField(tableA.Tax,' ',-1) As OtherTax
                              Resident tableA;
                              
                              DROP Table tableA;
                              
                              UNQUALIFY *;
                              

                               

                              (modifying your sample data to include a space within the country field as this only would explain the issue you described)

                               

                              hope this helps

                               

                              regards

                               

                              Marco