Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Hi
Try like this
Dimension:
Country
Expression
=Sum({<Country -= {'Brazil'}>}[No of Customers])
Try like
Sum( if(Country<>'Brazil', Customer)
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.
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
Hi,
use If(country<>'Brazil',[No of Customers])
Regards,
Sindu
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.
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);
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.
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