topic Re: Extracting first row within the row from a column in Water Cooler

Extracting first row within the row from a column

Anonymous — Wed, 06 Nov 2013 13:42:40 GMT

Hi,

How to load data from a column when we have multiple rows with in a row.

Example:

Header 1

Name

Steven
Joseph
Cruz

Alex
Yoh
Chang
Yan

Screw
Saw

Output should be:

Header 1
Name
Steven
Alex
Screw

Please help me on this example.

Re: Extracting first row within the row from a column

edg_baltazar — Wed, 06 Nov 2013 13:53:55 GMT

Hi Noble Raj!

When loading the XLSX on Qlikview , click on the CrossTable option on the wizard, I think this should help you... Regards!

Re: Extracting first row within the row from a column

Anonymous — Wed, 06 Nov 2013 13:57:30 GMT

Hi,

Here I have multiple rows in each row. Only first row need to be displayed on each row.

Re: Extracting first row within the row from a column

salto — Wed, 06 Nov 2013 13:58:11 GMT

Hello,

try

LOAD subfield(A, ' ' ,1) as Header

FROM

(ooxml, no labels, table is Source);

Hope this helps.

Re: Extracting first row within the row from a column

Wed, 06 Nov 2013 13:59:15 GMT

Hi, I Only Load excel file.

attach file.

Re: Extracting first row within the row from a column

Wed, 06 Nov 2013 14:05:01 GMT

It's great!. But, what if Column A contains a row with Stevie J. and other one con Steve ?

Re: Extracting first row within the row from a column

salto — Wed, 06 Nov 2013 14:09:28 GMT

Then it will retrieve a row with the string "Stevie" (discarding the "J." after the name) and other row with the string "Steve", since I am considering the blank after the name as the field separator...

Re: Extracting first row within the row from a column

Anonymous — Wed, 06 Nov 2013 14:11:29 GMT

Hi,

@ernesto

If we put cursor on Alex/Screw then we could see the remaining data.

@Salto

If we have spaces or blank in data then we may not get correct result.

Re: Extracting first row within the row from a column

salto — Wed, 06 Nov 2013 14:32:13 GMT

Hi Noble Raj,

Would it be possible to use a special character to work as "Name delimiter" in your Excel file?

Something, for example a pipe (|) that could set Steven J.|Joseph|Cruz so we could:

LOAD subfield(A, '|' ,1) as Header

Re: Extracting first row within the row from a column

Anonymous — Fri, 08 Nov 2013 05:03:54 GMT

Hi Salto,

No.

Even I tried with new line character '\n'. Do we have any option in Qlikview to handle next line?

Re: Re: Extracting first row within the row from a column

tresB — Fri, 08 Nov 2013 05:14:28 GMT

Use chr(10) for new line like:

LOAD

SubField(Name,chr(10),1) as FirstName

FROM <>;

PFA.

Re: Re: Extracting first row within the row from a column

Anonymous — Fri, 08 Nov 2013 06:16:40 GMT

Hi,

It worked well.

Many thanks.

Re: Re: Extracting first row within the row from a column

tresB — Fri, 08 Nov 2013 06:27:54 GMT

Please close the thread then, by marking the right post as answered. It would help others as well.