topic Setting a variable within the script in QlikView

Setting a variable within the script

samvile18 — Wed, 22 Aug 2012 11:37:49 GMT

Afternoon all,

I'm just wondering if the following is at possible?

I'm trying to set the variable vMaxDate to the maximum date from a list of measures within a QVD, the list of measures is:

KPI UploadDate

Actual 31/07/2012

Budget 31/07/2012

FTE 31/07/2012

Leavers 04/07/2012

Vacancies 29/07/2012

I want vMaxDate to be the maximum value for the KPI Actual....can anyone out there help please?

Any help is greatly appreciated.

Re: Setting a variable within the script

jagan — Wed, 22 Aug 2012 12:04:22 GMT

Hi,

First load the data into the QVW file, and then create a variable in Settings - Variable Overview and create new variable with

=Date(Max({1}UploadDate))

as expression this will always fetch the maximum date. i.e, (31/07/2012) irrespective of selections.

Hope this helps you.

Regards,

Jagan.

Re: Setting a variable within the script

samvile18 — Wed, 22 Aug 2012 12:10:27 GMT

This is what I thought it might look like, the problem I have though is I only want to use the value that's associated with the Actual KPI. The reason for this is because there may be another KPI with a later date that I don't want to use.

Re: Setting a variable within the script

gandalfgray — Wed, 22 Aug 2012 12:19:54 GMT

then you could create a temporary table in your load script:

tmp:

Load Max(UploadDate) As max_actual_upload_date

Resident <name_of_table>

Where KPI='Actual';

Let vMaxDate=Peek('max_actual_upload_date');

Drop Table tmp;

Re: Setting a variable within the script

samvile18 — Wed, 22 Aug 2012 12:26:39 GMT

This is what I do at the moment, the issue I have is when there is another KPI with a different date.

e.g. Actual 31/07/2012

Budget 06/08/2012

If I use the max command I end up setting the variable value as 06/08/2012, I don't want this to happen. I only want the variable to be the value of the Actual KPI.

Re: Setting a variable within the script

samvile18 — Wed, 22 Aug 2012 12:45:10 GMT

Hi chaps,

Just to let you know, I figured it out...like everything I was thinking too complex and all I needed to do was add a where clause to the temp table:

TempUploadMonth:

Load Distinct

Max(UploadDate) as MaxDate

Resident UploadMonth

Where KPI='Actual';

Thanks for taking the time to look at this with me