Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Creating Crosstable

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;

2 Replies
Highlighted
Master II
Master II

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

Highlighted
Specialist
Specialist

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