topic Load carriage return-ed fields as separate values? in QlikView

Load carriage return-ed fields as separate values?

Thu, 13 Jun 2013 19:57:22 GMT

I have a field (ITEMS) in the database I'm using in which the data entry team uses carriage returns for additional items:

ORDER_NUMBER	ITEMS
96496498	ball umbrella pool cue frog
94165498	zydeco ocarina guitar

ORDER_NUMBER

ITEMS

96496498

ball

umbrella

pool cue

frog

94165498

zydeco

ocarina

guitar

Is there any way to dice up ITEMS in a different field like ITEMS_2, so each item is a different value/row, but still associated to the ORDER_NUMBER? Like this:

ORDER_NUMBER	ITEMS_2
96496498	ball
	umbrella
	pool cue
	frog
94165498	zydeco
	ocarina
	guitar

Re: Load carriage return-ed fields as separate values?

swuehl — Thu, 13 Jun 2013 20:34:34 GMT

You can probably use Subfield() function for this, like

LOAD ORDER_NUMBER,

ITEMS,

SubField(ITEMS, chr(13)) as SUBITEM;

//Just a demo string

LOAD 'String1'&chr(13)&'String2'&chr(13)&'String3' as ITEMS,

1234 as ORDER_NUMBER

AutoGenerate 1;

You'll need to find the correct ascii code for your separator.

Re: Load carriage return-ed fields as separate values?

Mon, 17 Jun 2013 12:58:56 GMT

This is the code that worked for my particular situation:

SubField

(ITEMS, '
', ) as [SUB_ITEMS]

;

- instead of using a character code of 0A which wasn't working for the parsing, I just used pressed "Enter".