String Split & Structuralization & Alignment by Rows

Question

I have a bit weird scenario where I need to fetch data. I have the following three products:

product1

product2

product3

 

Each product has different ids (e.g. p1345, p3453, p2345) and different options which are having different skus:

 

option1(sku234),option2(sku345)

option1(sku1001)

option1(sku0022),option2(sku0033)

 

So if I store products in one CSV file and options in another CSV file, how can I fetch the relevant skus from the option file?

 

Answer

The first and most natural solution is to JOIN the two files and get skus through a computed column. Java takes a roundabout route to achieve this, yet it’s much simpler to express the algorithm in SPL (Structured Process Language):

A

1

=file("d:\\product.csv").import@i()

2

=file("d:\\option.csv").import@i()

3

=join@p(A1,A2.(~.array(",").(mid(~,start=pos(~,"(")+1,pos(~,")")-start))))

4

=A3.conj(#2.new(A3.#1:product,~:sku))

A1: Read in each line of product.csv as a member and return a sequence.

undefined

A2: Read in each line of option.csv as a member and return a sequence.

undefined

A3: Split skus from each member of A2’s sequence and join them with A1.

undefined

A4: Create a new table sequence. Join each product with the corresponding skus respectively and populate values to the new table sequence.

undefined

The SPL script can be easily integrated into a Java program through esProc JDBC. Details are explained in How to Call an SPL Script in Java