Creating Crosstable

[tmumaw]

Hi All,

I'm trying to create a crosstable with my types going across (STYPE).  For some reason I'm getting an error. What am I missing?  Thanks

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

Temp:
CrossTable(SType, Values, 9)
Load
Acct.AccountId,
Oppt.OpportunityId,
Oppt.StateCode,
Oppt.StatusCode, 
Oppt.new_Salesperson,
[Act_Date],
[Est_Date], 
Oppt.new_CostCenter,
// SType,
ActAmt
Resident Opportunity
;

Drop table Opportunity;


Rename Table Temp to Opportunity;

[m_woolf]

Thomas,

Isn't Crosstable used when you have data where the STYPES already go across the top and you want to make a regular table?

I think Generic Load might be what you are after.

The Generic Load

[tmumaw]

Need a little help, never tried one of these before.  Here is my script.  I am getting SType both across and up and down.  How can I get it only across?

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