Greater Than 1

[tmumaw]

We are an SAP shop.  I have a customer which may belong to one sales area or multiple sales areas.  I only want the ones that are associated with 2 or more sales areas.  I have attached my script.

Thanks

KNA1_SHPTO:
LOAD %KNA1 as %KNA1_SHPTO,
[Ship To],
[Customer Number] as [Customer Number SHPTO],
[Customer - Code & Name] as [Customer - Code & Name SHPTO],
[Customer - Code & Name] as [Property - Code & Name],
[Customer Name] as [Customer Name SHPTO],
[Customer Name] as [Property Name],
[Customer Address] as [Customer Address SHPTO],
Upper([Customer City]) as [Property City],
[Customer County] as [Property County],
[Customer State] as [Property State],
[Customer Zip] as [Property Zip],
[Customer Address] as [Property Address],
Upper([Customer City]) as [Customer City SHPTO],
[Customer County] as [Customer County SHPTO],
[Customer State] as [Customer State SHPTO],
[Customer Zip] as [Customer Zip SHPTO],
if ([Customer Country] = 'CA',
mid([Customer Zip],1,3), 
mid([Customer Zip],1,5))  as  [Cust Zip 3_5 SHPTO],
if ([Customer Country] = 'CA',
mid([Customer P.O. Zip],1,3),
mid([Customer P.O. Zip],1,5)) as [Cust P.O. Zip 3_5 SHPTO], 
[Customer Country] as [Customer Country SHPTO],
[Customer Country] as [Property Country],
[Customer P.O. Box] as [Customer P.O. Box SHPTO],
[Customer P.O. Zip] as [Customer P.O. Zip SHPTO],
[Customer Industry] as [Customer Industry SHPTO],
[Customer Type] as [Customer Type SHPTO],
[Customer AddrKey] as [Customer AddrKey SHPTO],
CustSince as [CustSince SHPTO],
Year(CustSince) as [YRCustSince SHPTO],
[Customer Phone] as [Customer Phone SHPTO],
[Customer Phone] as [Property Phone],
[Created By] as [Created By SHPTO],
[Evergreen Ind] as [Evergreen Ind SHPTO],
[Attr1] as [Do Not Mail SHPTO],
Attr2   as [Special Customer SHPTO],
If(Len(Trim(Blocked))=0 or Isnull(Blocked) , ' ', 'Y') as [Property Blocked],
If(Len(Trim(Marked4Deletion))=0 or Isnull(Marked4Deletion) , ' ', 'Y') as [Property Deleted]
FROM
D:\Qlikview\New Sales\QVD\KNA1.qvd
(qvd);

KNVV_SHPTO:
LOAD [Ship To] as [%KNA1_SHPTO],
[Sales Org] & DC & Division as [SalesArea SHPTO], 
1 as SalesArea
FROM

(qvd);
Exit Script;

[tmumaw · Accepted solution]

Figured it out.  See last part of script.....

KNVV_SHPTO:
LOAD [Ship To] as [%KNA1_SHPTO],
[Sales Org] & DC & Division as [SalesArea SHPTO], 
1 as SalesArea
FROM

(qvd);

Left Join (KNVV_SHPTO)
//KNA1_SHPTO:
LOAD %KNA1 as %KNA1_SHPTO,
[Ship To],
[Customer Number] as [Customer Number SHPTO],
[Customer - Code & Name] as [Customer - Code & Name SHPTO],
[Customer - Code & Name] as [Property - Code & Name],
[Customer Name] as [Customer Name SHPTO],
[Customer Name] as [Property Name],
[Customer Address] as [Customer Address SHPTO],
Upper([Customer City]) as [Property City],
[Customer County] as [Property County],
[Customer State] as [Property State],
[Customer Zip] as [Property Zip],
[Customer Address] as [Property Address],
Upper([Customer City]) as [Customer City SHPTO],
[Customer County] as [Customer County SHPTO],
[Customer State] as [Customer State SHPTO],
[Customer Zip] as [Customer Zip SHPTO],
if ([Customer Country] = 'CA',
mid([Customer Zip],1,3), 
mid([Customer Zip],1,5))  as  [Cust Zip 3_5 SHPTO],
if ([Customer Country] = 'CA',
mid([Customer P.O. Zip],1,3),
mid([Customer P.O. Zip],1,5)) as [Cust P.O. Zip 3_5 SHPTO], 
[Customer Country] as [Customer Country SHPTO],
[Customer Country] as [Property Country],
[Customer P.O. Box] as [Customer P.O. Box SHPTO],
[Customer P.O. Zip] as [Customer P.O. Zip SHPTO],
[Customer Industry] as [Customer Industry SHPTO],
[Customer Type] as [Customer Type SHPTO],
[Customer AddrKey] as [Customer AddrKey SHPTO],
CustSince as [CustSince SHPTO],
Year(CustSince) as [YRCustSince SHPTO],
[Customer Phone] as [Customer Phone SHPTO],
[Customer Phone] as [Property Phone],
[Created By] as [Created By SHPTO],
[Evergreen Ind] as [Evergreen Ind SHPTO],
[Attr1] as [Do Not Mail SHPTO],
Attr2   as [Special Customer SHPTO],
If(Len(Trim(Blocked))=0 or Isnull(Blocked) , ' ', 'Y') as [Property Blocked],
If(Len(Trim(Marked4Deletion))=0 or Isnull(Marked4Deletion) , ' ', 'Y') as [Property Deleted]
FROM
D:\Qlikview\New Sales\QVD\KNA1.qvd
(qvd)
;

SalesAreaTemp:
load
%KNA1_SHPTO,
sum(SalesArea) as TotSalesArea
Resident KNVV_SHPTO
group by %KNA1_SHPTO
;
Exit Script;