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: 
Chanty4u
MVP
MVP

RE:Explaination

Hi all,

Can any one Explain the below code how it works?

My data has two fileds in excel

LOAD ColorNo,

     ColorCode

FROM

[C\...\....\ColorCodes.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let vNumberOfRows = NoOfRows('ColorCodes');

For vI = 0 to (vNumberOfRows - 1)

Let vVariable_Name = Peek('ColorNo',vI,'Expression');

Let [$(vVariable_Name)] = Peek('ColorCode',vI,'Expression');

Next

DROP Table ColorCodes;

1 Solution

Accepted Solutions
sunny_talwar

I think loading the data part is simple and I am sure you understand that part. For the next few lines this is what is going on:

Let vNumberOfRows = NoOfRows('ColorCodes');

Setting a variable for estimating how many rows of data is available in the Excel file we just loaded.

For vI = 0 to (vNumberOfRows - 1)

Start a for loop and loop through x number of times where x depends on the number of rows of data

     Let vVariable_Name = Peek('ColorNo',vI,'Expression');

     In each look vVariable_Name would pick the value of ColorNo and save it to vVariable_Name

     Let [$(vVariable_Name)] = Peek('ColorCode',vI,'Expression');

     Use the value if ColorNo as the variable Name and save the value of ColorCode in it

Next

Repeat the same step for all the rows.

View solution in original post

10 Replies
sunny_talwar

I think loading the data part is simple and I am sure you understand that part. For the next few lines this is what is going on:

Let vNumberOfRows = NoOfRows('ColorCodes');

Setting a variable for estimating how many rows of data is available in the Excel file we just loaded.

For vI = 0 to (vNumberOfRows - 1)

Start a for loop and loop through x number of times where x depends on the number of rows of data

     Let vVariable_Name = Peek('ColorNo',vI,'Expression');

     In each look vVariable_Name would pick the value of ColorNo and save it to vVariable_Name

     Let [$(vVariable_Name)] = Peek('ColorCode',vI,'Expression');

     Use the value if ColorNo as the variable Name and save the value of ColorCode in it

Next

Repeat the same step for all the rows.

MK_QSL
MVP
MVP

Let vNumberOfRows = NoOfRows('ColorCodes');

This will count the number of Rows for COlorCodes column and store in vNumberOfRows variable.

For vI = 0 to (vNumberOfRows - 1)

This is for loop... which will run one line at a time starting from 0 to vNumberOfRows-1 times

Let vVariable_Name = Peek('ColorNo',vI,'Expression');

this will pick ColorNo respect to ColorCodes from Expression table and store in vVariable_Name variable

This variable you can use as lable

Let [$(vVariable_Name)] = Peek('ColorCode',vI,'Expression');

The above vVariable_Name is now stored as an Expression.

This variable you can use as expression.

Next

This will make sure taht your next loop will run

DROP Table ColorCodes;

PradeepReddy
Specialist II
Specialist II

there is some gaps in the script... NoOfRows('ColorCodes') and Expression Table in Peek function..

trying to create variables with  the  'ColorNo' Values as Variable Names  and 'ColoeCode' values as the variable value....

We use this type of scripting in applications, when we defined the expressions/variables in excel; and reading the same thru qlikview application

Thanks

Pradeep

sasikanth
Master
Master

hi,

ColorCodes:

LOAD ColorNo,

     ColorCode

FROM

[C\...\....\ColorCodes.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let vNumberOfRows = NoOfRows('ColorCodes');

For vI = 0 to (vNumberOfRows - 1)

Let vVariable_Name = Peek('ColorNo',vI,'ColorCodes');

Let [$(vVariable_Name)] = Peek('ColorCode',vI,'ColorCodes');

Next

For every color Number you are assigning a color code ,

Chanty4u
MVP
MVP
Author

thnk u sunny...still am learner only.  thnks  for ur grt explntion and time.

always be in top

Thanks

Suresh

Chanty4u
MVP
MVP
Author

thnx manish...nice expln.  easy to u.stnd.

Chanty4u
MVP
MVP
Author

simple line Thnx sunny.

For every color Number you are assigning a color code ,

Chanty4u
MVP
MVP
Author

Pradeep we alrdy done this part in my application by my collegue as i cnt u.stnd how its works. now its clear.

And thnx for response.

jagan
Luminary Alumni
Luminary Alumni

Hi,

This code dynamically creates the variables for all the colors in the ColorCodes.xlsx file.

Regards,

jagan.