Weird requirement

[#1]

Hi All,

I have a table with a very weird column. There are multiple values stored in the column and are separated by a forward slash(‘/’). Also, the number of values in that column are fixed(in my case 7). Values are stored in the following format in the column:

Q34VS/V734.22/V55.90/S627.4////

V34VS/V12.2/MM5.55/T33.4////

There could be blank values in some of the “slots” as you can see in the last 3 places in the above example. There are 4 other columns in that table but those columns store usual numeric or text values like most of our tables do. Any idea how to get the data so that each value in the // slot is in a separate row such as below? Please advice.

Q34VS

V734.22

V55.90

S627.4

“blank”

“blank”

“blank”

[#2]

Hi Peaceout

You need to convert the data into columns, then use a "crosstable" function to turn the columns into  rows (works as like an "un-pivot" function

To convert them into columns, the easiest thing would be to specify the delimiter when loading, which you can do if it is loaded from a text file... if not, you can use a series of functions (there are a number of ways to do this) to manually separate the text into columns.

for example:

raw:

LOAD * INLINE [

    AA

    Q34VS/V734.22/V55.90/S627.4////

    V34VS/V12.2/MM5.55/T33.4////

];

left join (raw)

load AA,

left(AA,index(AA,'/',1)-1) as col1,

mid(AA,index(AA,'/',1)+1,index(AA,'/',2)-index(AA,'/',1)-1) as col2,

mid(AA,index(AA,'/',2)+1,index(AA,'/',3)-index(AA,'/',2)-1) as col3,

mid(AA,index(AA,'/',3)+1,index(AA,'/',4)-index(AA,'/',3)-1) as col4,

mid(AA,index(AA,'/',4)+1,index(AA,'/',5)-index(AA,'/',4)-1) as col5

resident raw;

To turn the columns back into rows, use the "crosstable" statement:

crosstabbed:

crosstable(Colnumber,DATA) load * resident raw;

This will produce a table like:

AA	Colnumber	DATA
Q34VS/V734.22/V55.90/S627.4////	col1	Q34VS
Q34VS/V734.22/V55.90/S627.4////	col2	V734.22
Q34VS/V734.22/V55.90/S627.4////	col3	V55.90
Q34VS/V734.22/V55.90/S627.4////	col4	S627.4
Q34VS/V734.22/V55.90/S627.4////	col5
V34VS/V12.2/MM5.55/T33.4////	col1	V34VS
V34VS/V12.2/MM5.55/T33.4////	col2	V12.2
V34VS/V12.2/MM5.55/T33.4////	col3	MM5.55
V34VS/V12.2/MM5.55/T33.4////	col4	T33.4
V34VS/V12.2/MM5.55/T33.4////	col5

Hope this helps with your far-out requirement

Regards,

Erica

[#3]

try with a loop like:

for i = 1 to 7

LOAD

     subfield(rawcolumn, '/', $(i)) as newcolumn

FROM datasource

next i

[hic]

Even simpler:

subfield(rawcolumn, '/') as newcolumn

I.e. if you omit the third parameter of the subfield function, the Load statement will loop over that record 7 times.