Hi All,
Need some help with a generic load (first time). I need to see STYPE going across top not down. I am missing something in my load, but can't figure it out. Here's what is getting generated from the generic load.
I need one line showing totals for each type (ENMT, NEW, OTH, RWLS, SNOW. It appears it duplicating values. Here is my script. Thanks for you help.
Opportunity:
LOAD
Oppt.AccountId as Acct.AccountId,
Oppt.OpportunityId,
Oppt.StateCode,
Oppt.StatusCode,
Oppt.new_Salesperson,
Oppt.ActualCloseDate as [Act_Date],
Oppt.EstimatedCloseDate as [Est_Date],
Oppt.new_CostCenter,
if(Oppt.new_SalesType = '70330350-FFB1-E411-80D6-005056801CBD','RWLS',
if(Oppt.new_SalesType = '72330350-FFB1-E411-80D6-005056801CBD','SNOW',
if(Oppt.new_SalesType = '14CF7AFA-30E0-E711-8102-005056801CBD','SNOW',
if(Oppt.new_SalesType = 'D4CB552F-B0F4-E711-8104-005056801CBD','SNOW',
if(Oppt.new_SalesType = '6A330350-FFB1-E411-80D6-005056801CBD','ENMT',
if(Oppt.new_SalesType = 'AEC295B7-7EDE-E611-80F4-005056801CBD','NEW',
if(Oppt.new_SalesType = 'AA642C47-1920-E511-80DB-005056801CBD','OTH',
if(Oppt.new_SalesType = '5B3F1037-180E-E511-80DA-005056801CBD','OTH',
if(Oppt.new_SalesType = '6C330350-FFB1-E411-80D6-005056801CBD','OTH',
if(Oppt.new_SalesType = '6E330350-FFB1-E411-80D6-005056801CBD','OTH',
if(Oppt.new_SalesType = '238BF323-180E-E511-80DA-005056801CBD','OTH',
if(Oppt.new_SalesType = '6360121C-180E-E511-80DA-005056801CBD','OTH',
if(Oppt.new_SalesType = 'AA642C47-1920-E511-80DB-005056801CBD','OTH',
if(Oppt.new_SalesType = 'B1F4532B-180E-E511-80DA-005056801CBD','OTH','')))))))))))))) as SType,
// if(Oppt.new_SalesType = '70330350-FFB1-E411-80D6-005056801CBD' or
// Oppt.new_SalesType = 'AEC295B7-7EDE-E611-80F4-005056801CBD',1,0) as NewSales,
// if(Oppt.new_SalesType = '6A330350-FFB1-E411-80D6-005056801CBD',1,0) as EnmtSales,
if(len(Oppt.ActualValue) = 0 or isnull(Oppt.ActualValue) or Oppt.ActualValue='', 0, Oppt.ActualValue) as ActAmt
// If(Oppt.EstimatedValue > 0,Oppt.EstimatedValue,0) as EstAmt,
// If(Oppt.ActualValue > 0,Oppt.ActualValue,0) as ActAmt,
// If(Oppt.new_PendingAmount > 0,Oppt.new_PendingAmount,0) as PendingAmt,
// If(Oppt.new_LostAmount > 0,Oppt.new_LostAmount,0) as LostAmt
FROM
(qvd);
Let vGenericTable = 'Generic';
$(vGenericTable):
GENERIC
Load
Acct.AccountId,
Oppt.new_Salesperson,
Year([Act_Date]) as Act_Year,
Oppt.new_CostCenter,
SType,
ActAmt
Resident Opportunity
;
Let vNewTable = 'Opportunity_New';
$(vNewTable):
Load distinct
Acct.AccountId,
Oppt.new_Salesperson,
Year([Act_Date]) as Act_Year,
Oppt.new_CostCenter,
SType
RESIDENT Opportunity;
DROP Table Opportunity;
FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), '$(vGenericTable).*');
NEXT i
FOR i = 1 to FieldValueCount('Tablename')
LET vTable = '['&FieldValue('Tablename', $(i))&']';
LEFT JOIN ('$(vNewTable)') LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i
drop table TableList;
