topic Re: Delimiting out and storing properly for an applymap in QlikView

Delimiting out and storing properly for an applymap

james_hanify — Fri, 03 Feb 2017 12:09:08 GMT

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
JIGSSALEI1234	on Despatch # D123 D124
JIGSSALEI1235	on Despatch # D125 D126 D127
JIGSSALEI1236	on Despatch # D128 D130 D131

Turns into:

Key	Message
JIGSSALEI1234	D123
JIGSSALEI1234	D124
JIGSSALEI1235	D125
JIGSSALEI1235	D126
JIGSSALEI1235	D127
JIGSSALEI1236	D128
JIGSSALEI1236	D130
JIGSSALEI1236	D131

Many thanks!

James

Re: Delimiting out and storing properly for an applymap

sunny_talwar — Fri, 03 Feb 2017 12:14:27 GMT

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

];

Re: Delimiting out and storing properly for an applymap

Anonymous — Fri, 03 Feb 2017 12:19:31 GMT

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,

Re: Delimiting out and storing properly for an applymap

vinieme12 — Fri, 03 Feb 2017 12:19:33 GMT

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

];

Re: Delimiting out and storing properly for an applymap

sunny_talwar — Fri, 03 Feb 2017 12:24:21 GMT

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

];

Re: Delimiting out and storing properly for an applymap

stabben23 — Fri, 03 Feb 2017 12:24:28 GMT

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"
];

Re: Delimiting out and storing properly for an applymap

james_hanify — Fri, 03 Feb 2017 12:31:47 GMT

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

James