Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(
Exit Script;
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
(
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;
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
(
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;