topic How to write an expression in script ?? in QlikView

How to write an expression in script ??

Thu, 13 Jan 2011 16:32:38 GMT

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.

How to write an expression in script ??

Thu, 13 Jan 2011 16:41:30 GMT

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

How to write an expression in script ??

Thu, 13 Jan 2011 17:30:40 GMT

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

How to write an expression in script ??

Thu, 13 Jan 2011 17:36:26 GMT

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

Try this


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

How to write an expression in script ??

Thu, 13 Jan 2011 17:51:19 GMT

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

How to write an expression in script ??

Thu, 13 Jan 2011 17:58:13 GMT

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?

How to write an expression in script ??

Thu, 13 Jan 2011 19:11:23 GMT

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

How to write an expression in script ??

Fri, 14 Jan 2011 11:54:11 GMT

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

How to write an expression in script ??

Fri, 14 Jan 2011 12:45:18 GMT

Hi,

What is the message of your script error?

Have you checked if your FROM sentence is correct?

Regards

How to write an expression in script ??

Fri, 14 Jan 2011 15:09:07 GMT

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

How to write an expression in script ??

johnw — Sat, 15 Jan 2011 02:37:08 GMT

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
;

How to write an expression in script ??

Mon, 17 Jan 2011 11:31:10 GMT

Hi John !

Thanks for you reply.

Your solution working like a rocket....

Thanks a lot!