Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rt_new_user
New Contributor II

Excluding an item from the list box and performing a function

Hi all,

can anyone please help with this problem as i am very new to QV and still learning my way around to use different expressions to get things done:

Need to Sum  of No of customers from the list box 'No of customers' for each country excluding Brazil.

Screen Shot 09-22-14 at 02.45 PM 001.JPG.jpg

1 Solution

Accepted Solutions
MVP
MVP

Re: Excluding an item from the list box and performing a function

Hi,

In the below load we are excluding the Australia-telstra

LOAD *,

if(Operator<>'AUSTRALIA - Telstra (505 01)' or Operator<>’Brazil – Vivo (278 02)’,Operator)as OperatorminusTelstra

FROM

Inbound_140917_0903.xlsx

(ooxml, embedded labels, table is Inbound_140917_0903);

On top of the above load statement we are creating the new Country and operator name columns.

LOAD

*,

SubField(OperatorminusTelstra, '-' ,1) as Country,

     SubField(OperatorminusTelstra, '-' ,2) as "Operator name";

This type of loading is called Precedence loading.

To reuse the script I used above method.  We can also do the same in below manner

LOAD *,

if(Operator<>'AUSTRALIA - Telstra (505 01)' or Operator<>’Brazil – Vivo (278 02)’, SubField(OperatorminusTelstra, '-' ,1)) as Country,

if(Operator<>'AUSTRALIA - Telstra (505 01)' or Operator<>’Brazil – Vivo (278 02)’, SubField(OperatorminusTelstra, '-' ,2)) as OperatorName,

if(Operator<>'AUSTRALIA - Telstra (505 01)' or Operator<>’Brazil – Vivo (278 02)’,Operator)as OperatorminusTelstra

FROM

Inbound_140917_0903.xlsx

(ooxml, embedded labels, table is Inbound_140917_0903);

Hope this helps you.  If you got the answer please close this thread by giving Correct and Helpful Answers.

Regards,

Jagan.

12 Replies

Re: Excluding an item from the list box and performing a function

Hi

Try like this

Dimension:

Country

Expression

=Sum({<Country -= {'Brazil'}>}[No of Customers])

senpradip007
Valued Contributor III

Re: Excluding an item from the list box and performing a function

Try like

Sum( if(Country<>'Brazil', Customer)

MVP
MVP

Re: Excluding an item from the list box and performing a function

Hi Ravi,

Use this set analysis expression

Expression

=Sum({<Country -= {'Brazil'}>}[No of Customers])

-= operator excludes the given values from Current possible values.

Hope this helps you.

Regards,

Jagan.

rt_new_user
New Contributor II

Re: Excluding an item from the list box and performing a function

I should have been more elaborative from the start:

Basically i have a field “Operator” which has data in the format for example (Countryname – Opeartor name)

I have used the subfield function to break it into two additional list boxes as below:

I have then used Dimension as a Group with both (“Country” and “Operator name”) for drill down purpose. As i want my chart to show firstly by Country and then by Operator. Aim is to exclude one Operator completely from the total, for example AUSTRALIA – Telstra (505 01) from the Operator list box (please note Operator list box has also been broken down to Country and Operator name

sindu_bharathi
New Contributor III

Re: Excluding an item from the list box and performing a function

Hi,

use If(country<>'Brazil',[No of Customers])

Regards,

Sindu

MVP
MVP

Re: Excluding an item from the list box and performing a function

Hi Ravi,

In script create a new field excluding the operator AUSTRALIA – Telstra (505 01), try script like below

LOAD

*,

SubField(NewField, '-', 1) AS NewCountry,

SubField(NewField, '-',  2) AS NewOperatorName;

LOAD

*,

If(Operator <>  'AUSTRALIA – Telstra (505 01)', Operator) AS NewField

FROM Data;


Hope this helps you.

Regards,

Jagan.

rt_new_user
New Contributor II

Re: Excluding an item from the list box and performing a function

Hi Jagan

Tried this but didn’t work. Got a”file not founf” error ☹

Directory;

LOAD Date,

Time,

Operator,

if(Operator<>'AUSTRALIA - Telstra (505 01)',Operator)as OperatorminusTelstra,

SubField(OperatorminusTelstra, '-' ,1) as Country,

SubField(OperatorminusTelstra, '-' ,2) as "Operator name",

FROM

Inbound_140917_0903.xlsx

(ooxml, embedded labels, table is Inbound_140917_0903);

MVP
MVP

Re: Excluding an item from the list box and performing a function

Hi,

Try this script

Directory;

LOAD

*,

SubField(OperatorminusTelstra, '-' ,1) as Country,

     SubField(OperatorminusTelstra, '-' ,2) as "Operator name";

LOAD Date,

     Time,

     Operator,

if(Operator<>'AUSTRALIA - Telstra (505 01)',Operator)as OperatorminusTelstra

FROM

Inbound_140917_0903.xlsx

(ooxml, embedded labels, table is Inbound_140917_0903);

Regards,

Jagan.

rt_new_user
New Contributor II

Re: Excluding an item from the list box and performing a function

Sort of worked partially as i don’t see the AUSTRALIA-Telstra (505 01) in Operator list box, however the list box ‘ No of customers’ has disappeared. I don’t see it in the list of available fields ☹

Can you also explain what your script does please?

Regards

Community Browser