Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sureshqv
Esteemed Contributor III

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

Re: RE:Explaination

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.

10 Replies

Re: RE:Explaination

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.

Re: RE:Explaination

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
Valued Contributor II

Re: RE:Explaination

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
Valued Contributor III

Re: RE:Explaination

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 ,

sureshqv
Esteemed Contributor III

Re: RE:Explaination

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

always be in top

Thanks

Suresh

sureshqv
Esteemed Contributor III

Re: RE:Explaination

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

sureshqv
Esteemed Contributor III

Re: RE:Explaination

simple line Thnx sunny.

For every color Number you are assigning a color code ,

sureshqv
Esteemed Contributor III

Re: RE:Explaination

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.

MVP
MVP

Re: RE:Explaination

Hi,

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

Regards,

jagan.

Community Browser