topic Re: How can i resolve problem with multi values in a cell in QlikView

How can i resolve problem with multi values in a cell

Mon, 26 Jan 2026 18:19:17 GMT

Hi All

I have a scenario where some fields have multi values with a separator in different columns, i need to separate these values and insert in new row , for example:

ID CustID Int_type int_amt

101 2001 PE%PS%PI 20.75%101%0

102 2002 PI%PE 1009%2.5

103 2003 PS%PE%PL 0%15%10

how do i want to load into qlikview is

ID SubID CustID Int_type int_amt

101 2001 PE%PS%PI 20.75%101%0

101 1 2001 PE 20.75

101 2 2001 PS 101

101 3 2001 PI 0

102 2002 PI%PE 1009%2.5

102 1 2002 PI 1009

102 2 2002 PE 2.5

103 2003 PS%PE%PL 0%15%10

103 1 2003 PS 0

103 2 2003 PE 15

103 3 2003 PL 10

Thanks in anticiapation.

Re: How can i resolve problem with multi values in a cell

Sat, 14 Sep 2013 05:33:48 GMT

will you upload sample application or explain breifly.............

Re: How can i resolve problem with multi values in a cell

Sat, 14 Sep 2013 05:48:49 GMT

Actally this is core application constrain, there are some columns those contain multi values in single cell. All values those are separated by a special character have different meanings. Suppose in column Interest_Type holding PE%PI%PS that means there are three entries, similarly Interest_Amount coloumn holding values of 101%2.5%40 that means PE=101, PI=2.5 and PS=40. The interest type conents are diffrentiating the meanings of amount interest_amount field. This is database structure , i need to tell qilikview that whene ever multi values come in these column it should consider it as new rows in same sequence as they are in cell.

Re: How can i resolve problem with multi values in a cell

tresB — Sat, 14 Sep 2013 05:49:47 GMT

You can do it using subfield() function. And to get the new field subID, there would several ways, but what comes in mind now is using index() in a tricky way (unfortunately can't try myself right now to give the exact script).

Re: How can i resolve problem with multi values in a cell

Sat, 14 Sep 2013 05:52:36 GMT

subfield() function will add extra column if i use index in syntax , without index it creats redundant rows for all records.

Re: How can i resolve problem with multi values in a cell

tresB — Sat, 14 Sep 2013 06:00:54 GMT

For now, leave the new field SubID, and try the code like below:

Final:

Load CustID,

ID,

int_type,

int_amt

From <> ;

Load CustID, // this will get auto concatenated

ID,

int_amt,

Subfield(int_type, '%') as int_type

From <>;

This should give you all what you want except the new SubID field.

Re: How can i resolve problem with multi values in a cell

Sat, 14 Sep 2013 06:13:33 GMT

Thansk for your reply, actually subfield work fine with one field like int_type but i do also need int_amt field to be separated in same sequence if i apply subfield() function in int_amt field it creats 9 rows against each ID which it should be 3 rows or based on separator used in field.

Re: How can i resolve problem with multi values in a cell

tresB — Sat, 14 Sep 2013 06:24:03 GMT

You are right, subfield will load for all the combinations, so if there are two subfields then loads would be in multipled times.If the number of sub strings are known and fixed (as in your case 3 here), alternatively you can try using repeatative iteration of the load using WHILE clause. put that iteration number in the subfield function which would be the string position indicator and common to both the fields.

Re: How can i resolve problem with multi values in a cell

Sat, 14 Sep 2013 06:42:40 GMT

problem is that separator are not identical they can be 2,3,4 or five.

Re: How can i resolve problem with multi values in a cell

Sat, 14 Sep 2013 19:15:30 GMT

any solution , would be great help.

Re: Re: How can i resolve problem with multi values in a cell

nagaiank — Sat, 14 Sep 2013 22:47:15 GMT

Are you using Pick-like multi-valued database as data source?

Attached is a solution to your problem.