How to link data with embedded foreign keys in another table?

[cyoung311]

I'm sure this has been asked before; if so, my apologies.

I have a table that contains a sales record of products...userID, sale,

The userID column is a foreign key into the user table with user information. The sale  column contains string information formatted like <productID> - <qty> - <price paid> , <productID> - <qty> - <price paid> , ... etc. So each comma-separated triplet contains a product ID value (foreign key into product table), a quantity value, and a price paid value. 

How can I unpack the string to represent a new table that references the product table (per productID) and then create a report based upon the userID to show all purchases by that userID (there may more than one record per userID and more than one purchase per record.

[dplr-rn]

Use subfield function

so for example your data is like below

userid	saleinfo
1	1-5-20,5-2-25,2-10-40
2	3-4-50,4-66-30

step 1  use subfield to split into multiplerows and then in precending load create columns you need

e.g. below

load userid
,SubField(prodinfo,'-',1) as ProdID
, SubField(prodinfo,'-',2) as Quantity
, SubField(prodinfo,'-',3) as PricePaid
;
load userid,SubField(saleinfo,',') as prodinfo
from
subfieldtest.xlsx
(ooxml, embedded labels, table is Sheet1);

result

userid	ProdID	Quantity	PricePaid
1	1	5	20
1	5	2	25
1	2	10	40
2	4	66	30
2	3	4	50

[cyoung311]

Thanks. That is pretty much what I need. I've been trying to get a construct similar to a while-loop to parse the cart string into individual item sale records and then while-loop the item sale records to look up product info and link to other things.

If I understand correctly, I should be able create this function similar to a "stored prodcedure" (in SQL) so that I can invoke it in other reports?

Thanks in advance!!!