Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Replace Expression code with EditScript code

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

1 Solution

Accepted Solutions
Highlighted
richnorris
Contributor II

Re: Replace Expression code with EditScript code

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.

View solution in original post

17 Replies

Re: Replace Expression code with EditScript code

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

Not applicable

Re: Replace Expression code with EditScript code

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

Re: Replace Expression code with EditScript code

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

miguelbraga
Valued Contributor III

Re: Replace Expression code with EditScript code

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,

MB

Highlighted
richnorris
Contributor II

Re: Replace Expression code with EditScript code

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.

View solution in original post

Not applicable

Re: Replace Expression code with EditScript code

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

you should try below in expression

=sum(LeadsByActiveContacts)


or any other relevant aggregation function

Not applicable

Re: Replace Expression code with EditScript code

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

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