Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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
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
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
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 *;
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],';,.*/:'), '--', '')
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