Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

zwyne
Contributor

Split single row with multiple line into different columns

Hi there,

PFA,

I have source data which Excel  spreadsheet , there are some records which have multiple entries. So I am trying to split the records into multiple columns so that I can use them in my expression calculations. So when I import them into QlikView, its just one line.

I have highlighted these records in yellow in the attached.

Please assist.

Thanks

4 Replies
Partner
Partner

Re: Split single row with multiple line into different columns

Hi Zwyne,
Do you want to split the records to multiple columns? or multiple rows?
if columns, what's the column name for every value?
if rows, I think you can use the subfield() function, but you need to make the rules,like
for column C, the rule may be Line Feed
for column D, the rule may be "and"
for column E, the rule may be ","
Aiolos Zhao
MVP
MVP

Re: Split single row with multiple line into different columns

I suggest that you also post the required data format - its not that clear from your description or your sample.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Split single row with multiple line into different columns

Is this what you want?

image.png

 

Table:
LOAD [MI ID], 
     [Business Area], 
     [Impacted Business/Function], 
     Trim(SubField([Responsible Business/Function], ' and ')) as [Responsible Business/Function],
     Country;
LOAD [MI ID], 
     [Business Area], 
     [Impacted Business/Function], 
     Trim(SubField([Responsible Business/Function], Chr(10))) as [Responsible Business/Function],
     Country;
LOAD *
Where Len(Trim([Impacted Business/Function])) > 0;
LOAD [MI ID], 
     [Business Area], 
     Trim(SubField([Impacted Business/Function], '  ')) as [Impacted Business/Function],
//     [Impacted Business/Function],
     [Responsible Business/Function], 
     Country;
LOAD *
Where Len(Trim(Country)) > 0;
LOAD [MI ID], 
     [Business Area], 
     [Impacted Business/Function], 
     [Responsible Business/
Function] as [Responsible Business/Function], 
     Trim(SubField(Country, ',')) as Country
FROM
[..\..\Downloads\Sample.xlsx]
(ooxml, embedded labels, table is Issues);
zwyne
Contributor

Re: Split single row with multiple line into different columns

Hi Sunny,

I actually wanted single row and create different columns for example

Responsible Business/Function_1,Responsible Business/Function_2 and so on

Similarly for the other columns

Impacted Business/Function_1,....

Country_1,2..

Thanks