Qlik Community

Qlik Sense Cloud Discussions

samuel_lin
Contributor

Load only certain content in each Excel cell

Hi there,

(1) If I have a column (.xlsx) that has multiple lines such as

          ColumnA

1             grapes

               watermelon

               cheese

is it possible to load only the second line? "watermelon" for all the data in Column A?

(2) Is it possible to load data within certain "symbols"? for example, only load a column's data within quotation, or within $$, or content after/before @

Thank you so much for your help and reading through to understand this discussion

Samuel

Tags (2)
4 Replies
Employee
Employee

Re: Load only certain content in each Excel cell

You can put some filters on your load using the WHERE clause:

load

     ColumnA

from <>

where ColumnA='watermelon'

you can use more advanced string manipulation. match() , wildmatch(), Index() , Mid(), subfield() may all be helpful functions to filter for rows that you describe. Here is a full list

http://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/Scripting/StringFunctions/str...

samuel_lin
Contributor

Re: Load only certain content in each Excel cell

Jonathan,

thank you for your reply.

will where ColumnA = 'watermelon' work if I don't know 'watermelon' exists? but do know that I want the second row of each cell?

Thanks!

Samuel

Employee
Employee

Re: Load only certain content in each Excel cell

It definitely won't.  Its a little tricky to figure out the row numbers in Excel, easier with SQL.

You could do the following which loads all the data , then just filters it down to the 2nd row. Convoluted i know. You could also consider adding a column to your excel where you enumerate the row and then filter on the row number in the WHERE.

Temp:

LOAD [ColumnA],

  rowno() as RowNum

FROM [lib://Temp/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1)

;

NoConcatenate

Data:

LOAD [ColumnA],

  RowNum

resident Temp

where RowNum=2;

drop table Temp;

reddys310
Honored Contributor II

Re: Load only certain content in each Excel cell

Hi Samuel,

iIf you always want the second row and Column A cell, you can even use the peek function:

Temp:

LOAD [ColumnA]

FROM [lib://Temp/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

LET Value = Peek('ColumnA', 2, 'Temp'));


So now Value has the cell value you need.

Community Browser