topic How to use When condition in the Edit Script in QlikView

How to use When condition in the Edit Script

Wed, 25 Nov 2020 16:16:04 GMT

How do i use When condition in the edit script of qlikview ? Please do not suggest me to do this in Expressions. I want to add the condition in the edit script itself.Is this possible in below context? Please see the text marked in red below where i need the condition.

Following is the script,

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

COD_MARKET as Market,

Brand_Desc as Brand,

[Source Channel],

[Active Contacts],

[Unique Leads last 6 Months],

[Unique Leads 1 Month],

[Unique Permissions],

Total_Permission,

Contacts,

sum([Unique Leads 1 Month])/sum([Active Contacts]) as LeadsByActiveContacts (I want to add a condition to this expression for Market = Italy. How do i do this? )

FROM

(ooxml, embedded labels, table is Sheet2)

Group By Month,

COD_MARKET,

Brand_Desc,

[Source Channel],

[Active Contacts],

[Unique Leads last 6 Months],

[Unique Leads 1 Month],

[Unique Permissions], Contacts,Total_Permission;

Re: How to use When condition in the Edit Script

tamilarasu — Fri, 20 May 2016 14:14:54 GMT

Hi Maneck,

May be this,

Load ...

...

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

From

...

Re: How to use When condition in the Edit Script

Kushal_Chawda — Fri, 20 May 2016 14:22:30 GMT

may be this

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

COD_MARKET as Market,

Brand_Desc as Brand,

[Source Channel],

[Active Contacts],

Sum([Unique Leads last 6 Months]) as [Unique Leads last 6 Months],

sum([Unique Leads 1 Month]) as [Unique Leads 1 Month],

[Unique Permissions],

Total_Permission,

Contacts,

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

FROM

(ooxml, embedded labels, table is Sheet2)

Group By Month,

COD_MARKET,

Brand_Desc,

[Source Channel],

[Active Contacts],

[Unique Permissions], Contacts,Total_Permission;

Update : Do the sum for all the measures and remove from Group by.

Re: How to use When condition in the Edit Script

PabloOrtiz — Fri, 20 May 2016 14:39:28 GMT

Hello, maybe other way...

table:

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

COD_MARKET as Market,

Brand_Desc as Brand,

[Source Channel],

[Active Contacts],

[Unique Leads last 6 Months],

[Unique Leads 1 Month],

[Unique Permissions],

Total_Permission,

Contacts,

sum([Unique Leads 1 Month])/sum([Active Contacts]) as LeadsByActiveContacts

FROM

(ooxml, embedded labels, table is Sheet2)

WHERE COD_MARKET = 'Italy'

Group By Month,

COD_MARKET,

Brand_Desc,

[Source Channel],

[Active Contacts],

[Unique Leads last 6 Months],

[Unique Leads 1 Month],

[Unique Permissions], Contacts,Total_Permission;

concatenate (table)

.....

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

.........

WHERE COD_MARKET <> 'Italy'

Group By Month,

COD_MARKET,

Brand_Desc,

[Source Channel],

[Active Contacts],

[Unique Leads last 6 Months],

[Unique Leads 1 Month],

[Unique Permissions], Contacts,Total_Permission;

Re: How to use When condition in the Edit Script

Fri, 20 May 2016 15:23:31 GMT

Thanks!

Re: How to use When condition in the Edit Script

Anonymous — Fri, 20 May 2016 15:43:47 GMT

Try this ,

If(Market =' Italy', sum([Unique Leads 1 Month])/sum([Active Contacts]),'Na' ) as LeadsByActiveContacts