topic Re: load single cell to variable in QlikView

load single cell to variable

Mon, 17 Mar 2014 15:17:20 GMT

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

Re: load single cell to variable

Mon, 17 Mar 2014 15:21:57 GMT

Hi!

Try this:

T1:

LOAD * INLINE [

Country, Value

UK, 1000

US, 2000

China, 3000

];

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

Re: load single cell to variable

Mon, 17 Mar 2014 15:26:11 GMT

You can use peek() function.

LOAD * INLINE [

Country,Value

UK,1000

US,2000

China,3000

];

let vValue= peek('Value',1)

Re: load single cell to variable

Mon, 17 Mar 2014 15:26:51 GMT

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:

######################################################################
#A	China
# B	2000
#C	otherdata
# D	Sun. 33 Mar. 2014
# E	Report
######################################################################

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

Re: load single cell to variable

Mon, 17 Mar 2014 15:29:19 GMT

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

Like,

Drop Table ExcelFileName ;

Re: load single cell to variable

Mon, 17 Mar 2014 15:31:11 GMT

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

Re: load single cell to variable

Mon, 17 Mar 2014 15:33:43 GMT

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

Re: load single cell to variable

Mon, 17 Mar 2014 15:35:41 GMT

you can use First() function.

First 5 Load * from Excel.xls;

Re: load single cell to variable

iktrayanov — Mon, 17 Mar 2014 15:44:32 GMT

Country:

LOAD Country,

Value

FROM

[..\Cell to variable.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE(Country = 'US');

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