field not found

[#1]

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;

[MarcoWedel]

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

[MarcoWedel]

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

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

[#4]

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

[MarcoWedel]

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

[#6]

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 *;

[jonathandienst]

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],';,.*/:'), '--', '')

[MarcoWedel]

Hi,

you also could use KeepChar instead of PurgeChar like this:

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