Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
Hi Maneck,
May be this,
Load ...
...
If(COD_MARKET = 'Italy' , sum([Unique Leads 1 Month])/sum([Active Contacts]), '') as LeadsByActiveContacts
From
...
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.
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;
Thanks!
Try this ,
If(Market =' Italy', sum([Unique Leads 1 Month])/sum([Active Contacts]),'Na' ) as LeadsByActiveContacts