Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Are you using Pick-like multi-valued database as data source?
Attached is a solution to your problem.
will you upload sample application or explain breifly.............
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.
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).
subfield() function will add extra column if i use index in syntax , without index it creats redundant rows for all records.
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.
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.
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.
problem is that separator are not identical they can be 2,3,4 or five.
any solution , would be great help.