Uploading Variables

Anonymous — Wed, 15 Feb 2017 11:19:44 GMT

Hi all,

I am trying to upload a list of variables into my script from an excel file - previously I had a tab on the UI that I manually updated each month but wanted to automate it as much as possible.

I have created a Load statement as below but the variables do not seem to be changing when I reload. I use the variables later in the script to calculate costs e.g Hourly rate x Time on site etc.

Would be grateful for some help!

LOAD Variable,

Value

FROM

[Mapping data\Variables.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let $([Subcontractors]) =[Subcontractors];

Let $([Materials]) = [Materials];

Let $([Hourly Rate]) = [Hourly Rate];

Let $([Delivery Cost]) = [Delivery Cost];

Re: Uploading Variables

ahaahaaha — Wed, 15 Feb 2017 14:22:53 GMT

Hi Jenny,

Maybe so?

LOAD Variable,

Value

FROM

[Mapping data\Variables.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let $([Subcontractors]) = $([Subcontractors]);

Let $([Materials]) = $([Materials]);

Let $([Hourly Rate]) = $([Hourly Rate]);

Let $([Delivery Cost]) = $([Delivery Cost]);

Regards,

Andrey

Re: Uploading Variables

Anonymous — Wed, 22 Feb 2017 12:25:13 GMT

Hi Andrey,

I got the variables to work last week by using the below script:

Let vVariable1 = fieldvalue('Variable',1);

let [Subcontractors Birmingham] = fieldvalue('VarValue',1);

let $(vVariable1) = $(Subcontractors Birmingham);

Let vVariable2 = fieldvalue('Variable',2);

let [Materials Birmingham] = fieldvalue('VarValue',2);

let $(vVariable2) = $(Materials Birmingham);

Let vVariable3 = fieldvalue('Variable',3);

let [Hourly Rate Birmingham] = fieldvalue('VarValue',3);

let $(vVariable3) = $(Hourly Rate Birmingham);

Let vVariable4 = fieldvalue('Variable',4);

let [Delivery Cost Birmingham] = fieldvalue('VarValue',4);

let $(vVariable4) = $(Delivery Cost Birmingham);

etc, etc.

It worked last week but now comes up with the below error - it looks like it is using the first few variables from my table but then not the rest?

Error in expression:

* is not a valid function

ServData:

LOAD [Contract Number] as [Contract Num],

[Premises Number],

[Service Branch Number],

[Service Branch Name],

[Negotiating Branch Number],

[Negotiating Branch Name],

[Total time],

[Total Calls],

[Contract Number]&'-'& [Premises Number] as UID,

applymap('KAlocal',[Negotiating Branch Number],'Other') as [KA/Local2],

applymap('ProductDesc2',[Product Code],'Other') as [ProductDesc],

applymap('ProductGroup2',[Product Code],'Other') as [ProductGroup],

applymap('CallCategory', [Negotiating Branch Number],'Other') as CallCategory,

if([Service Branch Number]='21' and ([Negotiating Branch Number]<>'98'),20.79*([Total time]/60),

if([Service Branch Number]='22' and ([Negotiating Branch Number]<>'98'),19.25*([Total time]/60),

if([Service Branch Number]='23' and ([Negotiating Branch Number]<>'98'),*([Total time]/60),

if([Service Branch Number]='24' and ([Negotiating Branch Number]<>'98'),*([Total time]/60),

if([Service Branch Number]='26' and ([Negotiating Branch Number]<>'98'),*([Total time]/60),

if([Service Branch Number]='27' and ([Negotiating Branch Number]<>'98'),*([Total time]/60),

if([Service Branch Number]='21' and ([Negotiating Branch Number]='98'),*([Total time]/60),

if([Service Branch Number]='22' and ([Negotiating Branch Number]='98'),*([Total time]/60),

if([Service Branch Number]='23' and ([Negotiating Branch Number]='98'),*([Total time]/60),

if([Service Branch Number]='24' and ([Negotiating Branch Number]='98'),*([Total time]/60),

if([Service Branch Number]='26' and ([Negotiating Branch Number]='98'),*([Total time]/60),

if([Service Branch Number]='27' and ([Negotiating Branch Number]='98'),*([Total time]/60),0)))))))))))) as [Direct Service Cost],

if([Service Branch Number]='21' and ([Negotiating Branch Number]<>'98'),120.72*[Total Calls],

if([Service Branch Number]='22' and ([Negotiating Branch Number]<>'98'),130.32*[Total Calls],

if([Service Branch Number]='23' and ([Negotiating Branch Number]<>'98'),*[Total Calls],

if([Service Branch Number]='24' and ([Negotiating Branch Number]<>'98'),*[Total Calls],

if([Service Branch Number]='26' and ([Negotiating Branch Number]<>'98'),*[Total Calls],

if([Service Branch Number]='27' and ([Negotiating Branch Number]<>'98'),*[Total Calls],

if([Service Branch Number]='21' and ([Negotiating Branch Number]='98'),*[Total Calls],

if([Service Branch Number]='22' and ([Negotiating Branch Number]='98'),*[Total Calls],

if([Service Branch Number]='23' and ([Negotiating Branch Number]='98'),*[Total Calls],

if([Service Branch Number]='24' and ([Negotiating Branch Number]='98'),*[Total Calls],

if([Service Branch Number]='26' and ([Negotiating Branch Number]='98'),*[Total Calls],

if([Service Branch Number]='27' and ([Negotiating Branch Number]='98'),*[Total Calls],0)))))))))))) as [Delivery Cost]

FROM

[Visits\Visits*.xlsx]

(ooxml, embedded labels, table is Data)

WHERE([Service Date] > '31/01/2016' and [Service Date] < '01/02/2017')

Re: Uploading Variables

marcus_sommer — Wed, 22 Feb 2017 15:32:23 GMT

Take a look on this: http://www.qlikfix.com/2011/09/21/storing-variables-outside-of-qlikview/ how variables could be externally maintained and loaded within a small loop.

- Marcus

Re: Uploading Variables

Anonymous — Wed, 22 Feb 2017 17:54:54 GMT

Hi Marcus,

This is amazing! I have got the previous script to work by changing some of the names (I think it was confused with similar names) but this script simplifies it all even further

Thank you so much!!

topic Uploading Variables in QlikView

Uploading Variables

Re: Uploading Variables

Re: Uploading Variables

Re: Uploading Variables

Re: Uploading Variables