topic Re: How to split ";" values in a field into seperate rows and tables? in App Development

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

jblomqvist — Fri, 28 Sep 2018 16:04:35 GMT

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?

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

dplr-rn — Fri, 28 Sep 2018 16:41:12 GMT

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;

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

rwunderlich — Fri, 28 Sep 2018 17:36:47 GMT

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