Skip to main content

Water Cooler

This space is for everyone to ask questions related to the Community Platform. It's a space for us to get to know each other and have some fun! Come in and gather around the Water Cooler!

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zwyne
Creator
Creator

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
uacg0009
Partner - Specialist
Partner - Specialist

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
jonathandienst
Partner - Champion III
Partner - Champion III

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
sunny_talwar

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

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