Qlik Community

Ask a Question

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!

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
nnellore
Contributor II
Contributor II

Extracting first row within the row from a column

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.

12 Replies
edg_baltazar
Creator
Creator

Hi Noble Raj!

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

crosstable.png

nnellore
Contributor II
Contributor II
Author

Hi,

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

salto
Specialist II
Specialist II

Hello,

try

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

FROM

(ooxml, no labels, table is Source);

Hope this helps.

Not applicable

Hi, I Only Load excel file.

attach file.

Captura.PNG

Not applicable

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

salto
Specialist II
Specialist II

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...

nnellore
Contributor II
Contributor II
Author

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.

salto
Specialist II
Specialist II

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

nnellore
Contributor II
Contributor II
Author

Hi Salto,

No.

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