Excluding an item from the list box and performing a function

[rt_new_user]

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.

[jagan]

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.

[rt_new_user]

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

[jagan · Accepted solution]

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.