topic Re: Split one column data into two columns in Qlik in QlikView

Split one column data into two columns in Qlik

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have data like below.

LOAD * INLINE [
COUNTRY AND STATE,VALUE
INDIA,100
BANGALORE,25
MUMBAI,50
DELHI,25
U.S.,100
TEXAS,50
CALIFORNIA,25
HAWAII,25
];

In this data i want to split data into two columns and output is like

COUNTRY	STATE	Value
INDIA	BANGALORE	25
INDIA	DELHI	25
INDIA	MUMBAI	50
U.S.	CALIFORNIA	25
U.S.	HAWAII	25
U.S.	TEXAS	50

Can any one help me on this?

Regards

Mahesh

Re: Split one column data into two columns in Qlik

qlikview979 — Mon, 27 May 2019 18:13:22 GMT

Can any one help me on this?

Re: Split one column data into two columns in Qlik

rubenmarin — Mon, 27 May 2019 18:41:38 GMT

Hi, that table doesn't says wich rows are countries in wich ones are cities, this sample is easy because you can fix that there is a country and exactly 3 cities after that one but with real data it won't work, at least you need something that identifies wich rows are countries.

Maybe using the numbers if a country always have 100 and none of the cities have 100 or be sure that all of countries have 100 and there will be at least one city with 100.

Re: Split one column data into two columns in Qlik

qlikview979 — Tue, 28 May 2019 03:42:24 GMT

Its a sample data,but my original data set format is same

Re: Split one column data into two columns in Qlik

rubenmarin — Tue, 28 May 2019 16:51:03 GMT

It's not about the format... it will be always 3 cities by country? How can you identify that a row is a city or a country? If it helps, this can work with this sample data:
Data:
LOAD * INLINE [
COUNTRY AND STATE,VALUE
INDIA,100
BANGALORE,25
MUMBAI,50
DELHI,25
U.S.,100
TEXAS,50
CALIFORNIA,25
HAWAII,25
];

SplitData:
LOAD
If(VALUE=100, [COUNTRY AND STATE], Peek(COUNTRY)) as COUNTRY,
If(VALUE<100, [COUNTRY AND STATE]) as STATE,
VALUE
Resident Data;

FinalData:
NoConcatenate
LOAD * Resident SplitData Where not IsNull(STATE);

DROP Table SplitData;
DROP Table Data;