Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi colleagues,
could you please help me with suggestion how to realize one task.
We have in database table with next values:
OurOrderNum | CustomerOrderNum | weight | price | DeliveryDate |
abc123 | 40001;40002;123;1321 | 1000 | 2000 | 21.05.2019 |
abc124 | 3434; 23422;40003;40004 | 2000 | 3500 | 23.05.2019 |
abc125 | 40005 | 1500 | 3000 | 25.05.2019 |
So in reference field we have some 'customer orders' grouped into 1 our internal order. There could be unlimited quantity of customer orders grouped into 1 internal order.
For further analysis we need to have table in a way where 1 line = 1 CustomerOrderNum and other columns just duplicated accordingly. For example:
CustomerOrderNum | OurOrderNum | weight |
40001 | abc123 | 1000 |
40002 | abc123 | 1000 |
123 | abc123 | 1000 |
1321 | abc123 | 1000 |
3434 | abc124 | 2000 |
23422 | abc124 | 2000 |
40003 | abc124 | 2000 |
40004 | abc124 | 2000 |
40005 | abc125 | 1500 |
So can you please suggest me how to prepare Load statement to parse field CustomerOrderNum (which is separated with ";" and could be with variable length and with variable CustomerOrderNum quantity per line), in the way that we receive resulting table like specified above.
Thank you!
Try this
Table:
LOAD SubField(CustomerOrderNum, ';') as CustomerOrderNum,
OurOrderNum,
weight,
price,
DeliveryDate;
LOAD * INLINE [
OurOrderNum, CustomerOrderNum, weight, price, DeliveryDate
abc123, 40001;40002;123;1321, 1000, 2000, 21.05.2019
abc124, 3434; 23422;40003;40004, 2000, 3500, 23.05.2019
abc125, 40005, 1500, 3000, 25.05.2019
];
Try this
Table:
LOAD SubField(CustomerOrderNum, ';') as CustomerOrderNum,
OurOrderNum,
weight,
price,
DeliveryDate;
LOAD * INLINE [
OurOrderNum, CustomerOrderNum, weight, price, DeliveryDate
abc123, 40001;40002;123;1321, 1000, 2000, 21.05.2019
abc124, 3434; 23422;40003;40004, 2000, 3500, 23.05.2019
abc125, 40005, 1500, 3000, 25.05.2019
];
Thank you so much! Exactly what required!