Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikview979
Specialist
Specialist

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

 

4 Replies
qlikview979
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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;