Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

load single cell to variable

Hi all,

Is there any way to load a single cell to a variable from a provided spreadsheet??

For example:

Lets say i have the below table on excel.

Country,Value

UK,1000

US,2000

China,3000

and I want to load the value 2000 to the variable vValue.

Is there any way to achieve that?? Or any condition that you can use to take row 2 and column 2 cell??

1 Solution

Accepted Solutions
Not applicable
Author

You can use peek() function.

LOAD * INLINE [

Country,Value

UK,1000

US,2000

China,3000

];

let vValue= peek('Value',1)

View solution in original post

8 Replies
Not applicable
Author

Hi!

Try this:

T1:

LOAD * INLINE [

    Country, Value

    UK, 1000

    US, 2000

    China, 3000

];

let vValue = FieldValue('Value',2);

Not applicable
Author

You can use peek() function.

LOAD * INLINE [

Country,Value

UK,1000

US,2000

China,3000

];

let vValue= peek('Value',1)

Not applicable
Author

This seems to help,

but what about data that are not in a table??

Can I load data from an excel only 2 rows and 2 columns from the entire file by using a condition, and then exctact the value??

For example:

######################################################################
#AChina
# B2000
#Cotherdata
# DSun. 33 Mar. 2014
# EReport
######################################################################

I just want the 2000 value to extract from the file.

Not applicable
Author

Load the excel file, then use the function to extract the data required, then drop the excel table.

Like,

Drop Table ExcelFileName ;

Not applicable
Author

The excel file is too big can I load only the first 2-3 rows somehow???

Not applicable
Author

Try it:

LOAD

     B as Value

FROM

[test.xlsx]

(ooxml, no labels)

where RecNo()=2;

let vValue = FieldValue('Value',1);

Load only B:2 cell from sheet1

Not applicable
Author

you can use First() function.

First 5 Load * from Excel.xls;

iktrayanov
Creator III
Creator III

Country:

LOAD Country,

     Value

FROM

[..\Cell to variable.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE(Country = 'US');

Let vValue = peek('Value',0);