How to split ";" values in a field into seperate rows and tables?

[jblomqvist]

Hi there,

I have a table like this:

CustomerName	Products	Countries
Nike	Shoes; Caps; Glasses	US; UK; Spain
Nissan	Car; Trucks; Bikes	Japan; UK; US; Germany

I would like to turn the Products and Countries values into rows per CustomerName.

Then what I want to do is store Products, and Countries values into separate tables so ultimately there are 3 tables, Customers, Products, and Countries.

Anybody know how to do this please?

[dplr-rn]

maybe something like below varied to your needs

Source_Data:

Load * Inline [

Group_Id,Items

1,"Bread,Biscuit,Chocolate"

2,"Jean,Shirt,Shoes"

];

NoConcatenate

LOAD

Group_Id,

SubField(Items,',') as Items

Resident Source_Data;

Drop Table Source_Data;

[rwunderlich]

A little more detail on Dilip's suggestion:

Customers:

LOAD

    CustomerName,

    Products,

    Countries

FROM [lib://data]

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

Products:

LOAD

CustomerName,

    SubField(Products, ';') as Product

Resident Customers;  

Countries:

LOAD

CustomerName,

    SubField(Countries, ';') as Country

Resident Customers;

DROP Field Products, Countries From Customers;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com