how to transpose with tjavarow with certain condition

[sakura99]

hallo,

I have to extract and transpose some of text with this kind of condition (delimiter "|" should be erased)

to summarize, the key of this problem is :

-how to separate by "|" (and transpose) ,only if there is value between "|" and "|"

-if there is no value, please erase the row. 

e.g :

||9000|-75000||2000| --> parse 3 values : 9000, -75000 and 2000

||3000|| --> parse 1 value : 3000

|||-100|||40||| --> parse 2 values: -100 and 40

input

COLUMN A// COLUMN B

1.X||9000|-75000||2000|

2.Y||3000||

3.Z|||-100|||40|||

4.C||

5.D||

expectable output:

COLUMN A// COLUMN B

1.X// 9000

1.X// -75000

1.X// 2000

2.Y// 3000

3.Z// -100

3.Z// 40

 (there are no C and D column because the value between || is null.)

I already tried using tNormalize, but I don't meet the desired result.

I guess I have to use tjavarow with regex pattern match function, but I don't know how to write it properly.

I really appreciate your time and help.

[sakura99]

Actually my data looks like this. Is it possible to build the script because the data will be dynamic (more than 40 millions of rows)?

[pakapi_]

My script does not have any rows limitations, so you can use it. Just add:

input_row.input.replace("^","") - you don't need ^ to properly do te job 🙂

As I mentioned, you have to think how to remove rows without any values after first column (like 4C and 5D). Just google it f.g. "java how to remove empty rows csv" 🙂