topic Re: Replace Expression code with EditScript code in QlikView

Replace Expression code with EditScript code

Fri, 20 May 2016 14:54:37 GMT

I am using following in the expression which works just fine(shown in red). For some other reasons(cant elaborate here) i want to replace below expression code(in red) with EditScript code(in green)

Expression Code :-

=sum({<Market={'Italy'}>}[Unique Leads 1 Month])/sum({<Market={'Italy'}>}[Active Contacts])

Edit Script Code:-

sum(if(COD_MARKET='Italy',[Unique Leads 1 Month]))/sum(if(COD_MARKET='Italy',[Active Contacts])) as LeadsByActiveContacts

I tried to do the following to replace the expression but it doesn't work!

=LeadsByActiveContacts

Re: Replace Expression code with EditScript code

Kushal_Chawda — Fri, 20 May 2016 15:20:04 GMT

Edit script code is fine.. what is issue there?

Re: Replace Expression code with EditScript code

Fri, 20 May 2016 15:23:02 GMT

i want to refer the Edit Script code in the Expression. how do i do this?

Re: Replace Expression code with EditScript code

sunny_talwar — Fri, 20 May 2016 15:29:52 GMT

Script itself ran, but the final output is not matching up? What is the issue we are trying to troubleshoot here?

Re: Replace Expression code with EditScript code

miguelbraga — Fri, 20 May 2016 15:32:27 GMT

Hi there,

Have you tried to remove the equal symbol in the expression. You can try to put a expression like this: LeadsByActiveContacts/1.

Hope it helps

Regards,

Re: Replace Expression code with EditScript code

richnorris — Fri, 20 May 2016 15:35:03 GMT

Dont you just want to do:

LET LeadsByActiveContacts = sum(if(COD_MARKET='Italy',[Unique Leads 1 Month]))/sum(if(COD_MARKET='Italy',[Active Contacts]))

and then where you previously had the code, do

=$(LeadsByActiveContacts) ?

/EDIT:

SO, your problem is, I assume in your code you're doing something like:

TableName:

LOAD

Things,

Stuff,

Dimensions,

etc,

sum(if(COD_MARKET='Italy',[Unique Leads 1 Month]))/sum(if(COD_MARKET='Italy',[Active Contacts])) as LeadsByActiveContacts

Resident someplace

or something to that effect. The trouble is doing it in a table load like that, is essentially running the aggregation (the summation) per row of that table. So, for every row its just going to sum that row, which will just be itself, it wont take any other rows into consideration. So for every row of your table, you'll have a field LeadsByActiveContacts which has the value in [Unique Leads 1 Month] for that row divided by the value in [active contracts] for that row. (If COD_MARKET is italy, etc).

You can do what you want using Aggr, but it starts to get messy.

Re: Replace Expression code with EditScript code

Fri, 20 May 2016 15:35:21 GMT

There is no better way to explain it. In simple terms i am asking how do i refer the EditScript code in this case in the Expression in Frontend

Re: Replace Expression code with EditScript code

Kushal_Chawda — Fri, 20 May 2016 15:37:08 GMT

you should try below in expression

=sum(LeadsByActiveContacts)

or any other relevant aggregation function

Re: Replace Expression code with EditScript code

Fri, 20 May 2016 15:44:52 GMT

I tried this already but sum(LeadsByActiveContacts) changes the final output that is the reason i just want to refer it as is in the frontend . Is there a way to do it without use of sum?

Re: Replace Expression code with EditScript code

sunny_talwar — Fri, 20 May 2016 15:52:13 GMT

Since the code is not converted into a field, all you have to do is refer to it as a field. May be first in a table box object to check if the numbers are making sense or not

Re: Replace Expression code with EditScript code

richnorris — Fri, 20 May 2016 15:52:33 GMT

Yeah, you do that like I said above. You store the code into a variable in edit script, and then in your code, you use the dollar expansion of that variable. It's actually quite good practice, as it means you can reuse the calculation in different expressions and tables without having to re type it out each time.

Re: Replace Expression code with EditScript code

Fri, 20 May 2016 16:04:52 GMT

This is what i tried to do as you mentioned. See the text in Bold below.This gives an error though when i load script.As the variables which are used in this statement are not yet loaded inot memory??

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET LeadsByActiveContacts = sum(if(COD_MARKET='Italy',[Unique Leads 1 Month]))/sum(if(COD_MARKET='Italy',[Active Contacts]));

LOAD date(Month,'MMM-YY') as Month,

COD_MARKET as Market,

Brand_Desc,

[Source Channel],

[Active Contacts],

[Unique Leads last 6 Months],

[Unique Leads 1 Month],

[Unique Permissions],

Total_Permission,

Contacts

FROM

(ooxml, embedded labels, table is Sheet2)

Re: Replace Expression code with EditScript code

Kushal_Chawda — Fri, 20 May 2016 16:10:24 GMT

=only(LeadsByActiveContacts)

Re: Replace Expression code with EditScript code

Fri, 20 May 2016 16:12:23 GMT

Already tried this too ! doesn't work. It will display blanks

Re: Replace Expression code with EditScript code

miguelbraga — Fri, 20 May 2016 16:17:38 GMT

Call the Let function after the Table load, It's better practice

Re: Replace Expression code with EditScript code

Kushal_Chawda — Fri, 20 May 2016 16:20:58 GMT

can you share sample application?

Re: Replace Expression code with EditScript code

Fri, 20 May 2016 16:25:08 GMT

If i try to place it after the table load it doesn't accept the "LET" function. I tried that too.

Can you modify the above code and show me what you mean?

Re: Replace Expression code with EditScript code

settu_periasamy — Fri, 20 May 2016 17:47:04 GMT

Hi,

Try this

In script - use 2 single quotes

LET LeadsByActiveContacts = 'sum(if(COD_MARKET=''Italy'',[Unique Leads 1 Month]))/sum(if(COD_MARKET=''Italy'',[Active Contacts]))';

Expression

$(LeadsByActiveContacts)