topic Re: Using Subfield, getting blank dimension in QlikView

Using Subfield, getting blank dimension

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Using Subfield, getting blank dimension

Nicole-Smith — Mon, 31 Oct 2016 17:44:09 GMT

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

Re: Using Subfield, getting blank dimension

Frank_Hartmann — Mon, 31 Oct 2016 18:06:43 GMT

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