Split one column data into two columns in Qlik

[qlikview979]

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

[qlikview979]

Can any one help me on this?

[rubenmarin]

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]

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

[rubenmarin]

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;