Solved: How to write an expression in script ?? - Qlik Community

Report Inappropriate Content · ‎2011-01-13

If 'x' is the table having 'a' & 'b' fields . I need something like,

Comm = .[ a ]*(.[ b ]/100)

How can I write in the script ??

Thanks in advance.

johnw · ‎2011-01-14

I believe it decided that "Total" is a file, and it used whatever your current path is to find that file. There's no such file, so it gives you an error. I don't have experience with access databases, but I believe you have to use the SQL keyword to tell it to send the next bit of script to the ODBC driver to pass to the database. Then you load from those results by using a preceeding load. Put it all together, and I think you want something like this:

LOAD [Inside %],
[Total Costs],
([Inside %]*([Total Costs]/100)) AS COMMISSION
;
SQL SELECT Inside %, Total Costs
FROM Total
;

View solution in original post

Report Inappropriate Content · ‎2011-01-13

Will that expression be part of the loading of table x from the database or QVD? Or will it be part of a Resident load of table x?

If so, you should be simple:

LOAD a, b, (a * (b / 100) As Comm

Report Inappropriate Content · ‎2011-01-13

Hi Miller

Thanks for your reply. Expression is a part of the loading table from the database (ACCESS)

Iam getting the script error. Here is my exp.

LOAD a %,b (a % * (b/100)) AS COMM

FROM X;

Any suggestions ??

Thanks

Report Inappropriate Content · ‎2011-01-13

You have '%' symbol after load a, replace it with ,

Try this


LOAD a ,
 b ,
 (a % * (b/100)) AS COMM
FROM X;

Report Inappropriate Content · ‎2011-01-13

Hi

Thanks for your reply. Still iam getting script error…might be because of % symbol.

Field names : a %, b

Table name : X

Here is my revised exp.

LOAD a ,

Total b ,

(a % * (b/100)) AS COMM

FROM X;

Any suggestions please ??

Thanks

Report Inappropriate Content · ‎2011-01-13

a % doesn't work as a field name without brackets. Field names with spaces (and I'm sure % signs) need brackets.

LOAD [a %],
 Total b ,
 ([a %] * (b/100)) AS COMM
FROM X;

What is Total b supposed to be doing?

Report Inappropriate Content · ‎2011-01-13

Total b should be enclose in the bracket as [Total b ]

Report Inappropriate Content · ‎2011-01-14

Hi

Thanks for your replies. I tried both the examples and still getting script error.

This is the actual measure :

COMMISSION = (Total Costs*(Inside %)/100)

'Total Costs' and 'Inside %' are the fields in 'Total' table. ODBC connected to ACCESS database.

I have written script like this :

LOAD [Inside %],

[Total Costs],

([Inside %]*([Total Costs]/100)) AS COMMISSION

FROM Total;

Note : Field names having spaces.

Can anyone help ???

Thanks

Report Inappropriate Content · ‎2011-01-14

Hi,

What is the message of your script error?

Have you checked if your FROM sentence is correct?

Regards

Report Inappropriate Content · ‎2011-01-14

Hi

script is like this:

LOAD [Inside %],

[Total Costs],

([Inside %]*([Total Costs]/100)) AS COMMISSION

FROM Total;

After execution, Iam getting script error like this :

Cannot open file 'C:\Documents and Settings\ Desktop\Total'

LOAD [Inside %],

[Total Costs],

([Inside %]*([Total Costs]/100)) AS COMMISSION

FROM Total

Any suggestions ???

Thanks