Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
qlikview979
Valued Contributor

Split one column data into two columns in Qlik

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

COUNTRYSTATEValue
INDIABANGALORE25
INDIADELHI25
INDIAMUMBAI50
U.S.CALIFORNIA25
U.S.HAWAII25
U.S.TEXAS50

 

Can any one help me on this?

Regards

Mahesh

 

Tags (1)
4 Replies
qlikview979
Valued Contributor

Re: Split one column data into two columns in Qlik

Can any one help me on this?

Re: Split one column data into two columns in Qlik

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.
qlikview979
Valued Contributor

Re: Split one column data into two columns in Qlik

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

Re: Split one column data into two columns in Qlik

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;