topic Split single row with multiple line into different columns in Water Cooler

Split single row with multiple line into different columns

zwyne — Tue, 15 Jan 2019 09:10:51 GMT

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

Re: Split single row with multiple line into different columns

uacg0009 — Tue, 15 Jan 2019 09:54:21 GMT

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

Re: Split single row with multiple line into different columns

jonathandienst — Tue, 15 Jan 2019 09:57:24 GMT

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

Re: Split single row with multiple line into different columns

sunny_talwar — Tue, 15 Jan 2019 12:23:39 GMT

Is this what you want?

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);

Re: Split single row with multiple line into different columns

zwyne — Tue, 15 Jan 2019 13:30:03 GMT

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