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.
A2: Read in each line of option.csv as a member and return a sequence.
A3: Split skus from each member of A2’s sequence and join them with A1.
A4: Create a new table sequence. Join each product with the corresponding skus respectively and populate values to the new table sequence.
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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL