Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Maxim_Skorik
New Contributor

Parsing String with multiple values into single column table

Hi colleagues,

could you please help me with suggestion how to realize one task.

We have in database table with next values:

OurOrderNumCustomerOrderNumweightpriceDeliveryDate
abc12340001;40002;123;13211000200021.05.2019
abc1243434; 23422;40003;400042000350023.05.2019
abc125400051500300025.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:

CustomerOrderNumOurOrderNumweight
40001abc1231000
40002abc1231000
123abc1231000
1321abc1231000
3434abc1242000
23422abc1242000
40003abc1242000
40004abc1242000
40005abc1251500

 

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! 

 

Labels (2)
1 Solution

Accepted Solutions

Re: Parsing String with multiple values into single column table

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
];
2 Replies

Re: Parsing String with multiple values into single column table

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
];
Highlighted
Maxim_Skorik
New Contributor

Re: Parsing String with multiple values into single column table

Thank you so much! Exactly what required!