Using Subfield, getting blank dimension

[#1]

Hello,

I have a field with what I thought were newline characters or carriage returns. I tried to replace these characters in the load script, but they still display. Here is a screenshot of the data:

And a screenshot of the chart:

That first entry in the chart is what is confusing me. Hopefully this is an easy question, even if it makes me look dumb...

Thanks!

[Nicole-Smith · Accepted solution]

It's happening because everything in your [Root Cause] column in Excel ends in a semicolon, and that is also what you are trying to use as a delimiter in subfield.  So when it is splitting it, it's trying to get whatever is after the last semicolon, which just happens to be an empty string.  If you strip off the ending semicolon, it fixes the issue:

LOAD [Report Number],

  Trim(SubField(left([Root Cause], len([Root Cause])-1),';')) as [Root Cause]

FROM

\\aoc.gov\network_shares$\User_Profiles\aglenden\Desktop\junk\data_sample.xlsx

(ooxml, embedded labels, table is Sheet1);

[Frank_Hartmann]

you could also try like this:

1:
LOAD [Report Number], 
     trim(SubField([Root Cause],';')) as [Root Cause]     
FROM
\\aoc.gov\network_shares$\User_Profiles\aglenden\Desktop\junk\data_sample.xlsx
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

2:
Load
[Report Number],
[Root Cause]
Resident 1 Where len([Root Cause])>0; DROP Table 1;

hope this helps