topic Re: separate two values in a field to rows for each value in QlikView

separate two values in a field to rows for each value

diwaskarki — Wed, 23 May 2018 18:28:34 GMT

I have a field called Owner that stores user ids like e1r1, mhtr, uert etc.

I am bringing this from a db2 field. Sometimes in each row there are multiple user ids separated by a comma.

I have another table called User_Info in qlikview, that stores first name, last name and user ids.

I need to correlate user ids from the Owner table to user ids in the USER_Info table but since there are more than one user ids in each row, I cant do that.

How do I separate user ids in the Owner table into a new row? Id appreciate the help.

Re: separate two values in a field to rows for each value

eduardo_dimperio — Wed, 23 May 2018 19:40:09 GMT

Hi Diwas,

try this

LOAD SubField(Owner table , ';') AS new_ID

RESIDENT yourtable;

Re: separate two values in a field to rows for each value

mikaelsc — Wed, 23 May 2018 19:44:26 GMT

the answer of Eduardo in combination with a crosstable() load statement

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/crosstable.htm

this crosstable statement will allow you to crreate a table containing 1 row per user

once you left join this table back to the original table, this will duplicate rows (not magic, that's what a join does)