Separate Field into Multiple (varying amount of Subfields)

[ciaran_mcgowan]

Hi all,

I have two files; Drivers & Delivery.

Drivers is small and contains personal information about driver details (Name, Age, etc.). Delivery contains information about individual deliveries but they are not associates to the Driver table because:

Date	From	To	Sent
01/12/2015	Depot	Shop A	Paul & John
02/12/2015	Depot	Shop B	Paul, John, Ringo & George
03/12/2015	Depot	Shop C	John, George & Ringo
04/12/2015	Depot	Shop D	Ringo

I need to separate out the Sent field so it will join to the Name field in the Driver table. I've created the following to determine how many Drivers were sent for each delivery:

if(SubStringCount(Sent,',')=0 AND SubStringCount(Sent,'&')=0, 1,

     SubStringCount(Sent,',')+2)

          AS DriverCount

Does anyone know the most efficient way to loop through the table in the script and separate each Driver into a new row?

[sunny_talwar · Accepted solution]

May be this in the script:

SubField(Replace(Sent, '&', ','), ', ') as Sent

Sample

Table:

LOAD Date,

     From,

     To,

     SubField(Replace(Sent, '&', ','), ', ') as Sent

FROM

[https://community.qlik.com/thread/197040]

(html, codepage is 1252, embedded labels, table is @1);

[ciaran_mcgowan]

Thanks for the speedy response.

I came across this issue a couple of years ago and remember having to loop through the table until the maxium DriverCount had been reached, but this actually worked a treat.

So much simpler, I didn't realise that Subfield() worked like that if a subfield number wasn't specified!

[sunny_talwar]

In the script it does. But I don't think it would work like this on the front end.

[ciaran_mcgowan]

Good to know. Thanks