Setting and variable only once

[#1]

Hi All,

   Is it possible to set a variable only once at the time of load.

What I am trying is to set the MEAN/AVG months in a variable, I need this value in chart.

I would have two values:

Tenure of the Employee in The organisation

and

Avg Tenure of all the employee

I am willing to chow this comparison in the chart.

How can i achiever this?

I tried Below in script editor, Thought this would just set once but 

Resource:

LOAD DISTINCT Resource_Id,

     Resource_Name,

     Gender,

     Role,

     Level,

     Primary_Skill,

     Is_Multiskilled,

     DOJ,

     Round((date(Today(),'MM/DD/YYY')-date(DOJ,'MM/DD/YYYY'))/30) as Tenure_in_org,

     Exit_Ind

FROM

(ooxml, embedded labels, table is Sheet1);

set vAvgOrgTenure = 'Avg(Tenure_in_org)' ;

[effinty2112 · Accepted solution]

Hi Nishant,

Try this instead:

TempAvg:

Load

Sum(Tenure_in_org)/Count(Resource_Id) as AvgOrgTenure

resident Resource;

Let vAvgOrgTenure = Peek('AvgOrgTenure');

Drop Table TempAvg;

[#3]

Wow.. Thanks!!

It worked like charm

Can you please explain this solution a bit. is it the RESIDENT keyword that made the difference. If yes, please let me know how.

Thanks again

[effinty2112]

Hi Nishant,

                   After your table is loaded we need to work out the avg value of Tenure_in_org then retrieve that value from wherever Qlikview is holding it and put it in a variable.

After loading the Resource table the value we're looking for doesn't yet exist in the data model. Your idea to apply the Avg to the field would be fine as an expression in an interface textbox when the entire load was completed but that approach is not open to us in the script.

A simple way to do it is to create a temporary table with one field and one record.

TempAvg:

Load

Sum(Tenure_in_org)/Count(Resource_Id) as AvgOrgTenure

resident Resource;

When this is done there will be a single value for the field AvgOrgTenure in the table TempAvg. I didn't use the Avg function though I probably could have but I prefer to work it out as a sum divided by a count.

The Peek function retrieves values from tables.

I could have written out the Peek function using the full syntax and that would be:

Peek('AvgOrgTenure',0,'TempAvg')

The first parameter is the field name I'm interested in. NOTE: the field name with single quotes around it is style to use here.

The second parameter is a number that tells QlikView which record to go to to retrieve the value, 0 means get the value from the first record, 1 from the second , etc. The index starts from 0 not 1. You can put negative values in this parameter too, -1 means get the value from the last record, -2 means get the second last record, etc. The default is -1, get the value from the last record added to the table, so I left this parameter blank.

The third parameter allows us to specify the table to look in but this is only necessary if the field occurs in more than one table which would mean it would be a key field creating an association between tables and that is not the case here.

So

Let vAvgOrgTenure = Peek('AvgOrgTenure');

places the value we are looking for into the variable.

Lastly we can drop the temporary table to keep thing tidy.

Hope this makes things clear, if not let me know.

Kind regards

Andrew

[#5]

Thaks alot Andrew. For the solution as well as detailed explanation   its always fun learning

[effinty2112]

Hi Nishant,

                    If you're satisfied that your question has been answered please consider marking my answer as correct.

Kind regards

Andrew

[effinty2112]

Nishant,

                    Thanks a lot for marking my answer correct.Please disregard my message before this one I wasn't aware you had done so already. I'm very glad I was able to help you.

Kind regards

Andrew