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: 
haribabugv
Creator
Creator

Set variable from the load table

Hello Users,

I am trying to set the variable from the excel table which is loaded.

The value of the variable is set based on the condition that it should pick the value of 0.75 of the total rows.

For example

Based on the attached excel sheet, value of the variable should be 'i' as it is the 0.75 of total rows from the excel (total rows=20, 0.75 of total row is 15(=20*.75=15),so 15th row of the excel sheet is 'i' from column ID.

Need to the set the variable value in load script not in chart.

Value.JPG

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try like:

Tab:

LOAD

          ID,

           RowNo() as RowNum

FROM

[Variable.xlsx]

(ooxml, embedded labels, table is Sheet1);


Let vNoOfRows=NoOfRows('Tab');

Let v75RowNum=FieldValue('RowNum', vNoOfRows*.75);

Let v75Value=Lookup('ID','RowNum', v75RowNum);

View solution in original post

3 Replies
tresesco
MVP
MVP

Try like:

Tab:

LOAD

          ID,

           RowNo() as RowNum

FROM

[Variable.xlsx]

(ooxml, embedded labels, table is Sheet1);


Let vNoOfRows=NoOfRows('Tab');

Let v75RowNum=FieldValue('RowNum', vNoOfRows*.75);

Let v75Value=Lookup('ID','RowNum', v75RowNum);

pooja_prabhu_n
Creator III
Creator III

Hi,

Temp:

LOAD ID,

RowNo() as RowNumber

FROM

[Variable.xlsx]

(ooxml, embedded labels, table is Sheet1)

;

let vNofrows=NoOfRows('Temp')*0.75;

LET Row= peek('ID', $(vNofrows)-1,'Temp');

Thanks,

Pooja

mscottiaawg
Contributor
Contributor

The "peek" function solved my issue. I've been trying to figure out how to dynamically write qvds using a value in a table and add a date to the end.