Uploading Variables

[Anonymous]

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];

[ahaahaaha]

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

[Anonymous]

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')

[marcus_sommer · Accepted solution]

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

[Anonymous]

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!!