Delimiting out and storing properly for an applymap

[james_hanify]

Hi all,

I've come into a scenario where I need to try and delimit out data which is on the same line and put them as new lines, i'm assuming some kind of loop is required but any assistance would be great.

My data structure is relatively simple as I use an SQL query to limit the results

Example data:

Key	Message
JIGS*SALE*I1234	on Despatch # D123 D124
JIGS*SALE*I1235	on Despatch # D125 D126 D127
JIGS*SALE*I1236	on Despatch # D128 D130 D131

Turns into:

Key	Message
JIGS*SALE*I1234	D123
JIGS*SALE*I1234	D124
JIGS*SALE*I1235	D125
JIGS*SALE*I1235	D126
JIGS*SALE*I1235	D127
JIGS*SALE*I1236	D128
JIGS*SALE*I1236	D130
JIGS*SALE*I1236	D131

Many thanks!

James

[sunny_talwar]

Try this:

Table:

LOAD Key,

  SubField(Message, ' ') as Message;

LOAD Key,

  SubField(Message, '# ', 2) as Message;

LOAD * INLINE [

    Key, Message

    JIGS*SALE*I1234, on Despatch # D123 D124

    JIGS*SALE*I1235, on Despatch # D125 D126 D127

    JIGS*SALE*I1236, on Despatch # D128 D130 D131

];

[Anonymous]

Hi James,

by using iter you can achieve above result example :

TABLE:

LOAD * INLINE [

    OBJECT, VALUE

    A, "123,456"

    B, 1234

    C, "136,1532,16323"

]

;

RESULT:

lOAD OBJECT, SubField(VALUE,',',ITERNO()) AS VALUE_RESULT

RESIDENT TABLE

WHILE(ITERNO()<=SubStringCount(VALUE,',')+1);

DROP TABLE TABLE;

Hope this helps,

[vinieme12]

or this

LOAD Key,

  subfield(right(Message,len(Message)-Index(Message,' #')-2),' ') as Message

;

Load * INLINE [

Key,Message

JIGS*SALE*I1234,on Despatch # D123 D124

JIGS*SALE*I1235,on Despatch # D125 D126 D127

JIGS*SALE*I1236,on Despatch # D128 D130 D131

];

[sunny_talwar · Accepted solution]

Look at that... this worked too (I didn't think it would work for some reason )

Table:

LOAD Key,

  SubField(SubField(Message, '# ', 2), ' ') as Message;

LOAD * INLINE [

    Key, Message

    JIGS*SALE*I1234, on Despatch # D123 D124

    JIGS*SALE*I1235, on Despatch # D125 D126 D127

    JIGS*SALE*I1236, on Despatch # D128 D130 D131

];

[stabben23]

Or this one:

Load Key,
(subfield(trim(SubField(Message,'#',-1)),' ')) as Message
;

Load * Inline [
Key, Message
"JIGS*SALE*I1234", "on Despatch # D123 D124"
"JIGS*SALE*I1235", "on Despatch # D125 D126 D127"
"JIGS*SALE*I1236", "on Despatch # D128 D130 D131"
];

[james_hanify]

Brilliant! Thanks for the quick response, so subfield was the one to do this very efficiently.

James