How can i resolve problem with multi values in a cell

[#1]

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.

[#2]

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

[#3]

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.

[tresesco]

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).

[#5]

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

[tresesco]

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.

[#7]

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.

[tresesco]

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.

[#9]

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

[#10]

any solution , would be great help.