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

Excluding an item from the list box and performing a function

rt_new_user — Mon, 22 Sep 2014 04:48:50 GMT

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.

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

MayilVahanan — Mon, 22 Sep 2014 04:50:57 GMT

Try like this

Dimension:

Country

Expression

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

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

senpradip007 — Mon, 22 Sep 2014 04:53:18 GMT

Try like

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

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

jagan — Mon, 22 Sep 2014 05:16:30 GMT

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.

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

rt_new_user — Mon, 22 Sep 2014 05:16:34 GMT

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

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

sindu_bharathi — Mon, 22 Sep 2014 05:19:29 GMT

Hi,

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

Regards,

Sindu

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

jagan — Mon, 22 Sep 2014 05:22:52 GMT

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.

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

rt_new_user — Mon, 22 Sep 2014 05:42:23 GMT

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);

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

jagan — Mon, 22 Sep 2014 05:50:58 GMT

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.

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

rt_new_user — Mon, 22 Sep 2014 06:09:59 GMT

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

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

jagan — Mon, 22 Sep 2014 06:20:40 GMT

Hi,

Try this script

Directory;

LOAD

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

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

LOAD *,

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

FROM

Inbound_140917_0903.xlsx

(ooxml, embedded labels, table is Inbound_140917_0903);

Regards,

jagan.

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

rt_new_user — Mon, 22 Sep 2014 06:35:15 GMT

Great it worked!!!

Can you please explain what your script is doing for me to understand the logic behind it?

Also if i want to add any other country-operator name to the exclusion list for ex Brazil – Vivo (278 02) then i suppose i do this:

Directory;

LOAD

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

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

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);

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

jagan — Mon, 22 Sep 2014 06:40:33 GMT

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.