Load generic table with more than one column as key

[#1]

I have a SQL generic table like this:

CREATE TABLE [dbo].[IN_Contracts](

  [Snapshot_Date] [datetime] NOT NULL,

  [Contract_ID] [char](10) NOT NULL,

  [Attribute_Name] [varchar](50) NOT NULL,

  [Attribute_Value_Text] [varchar](8000) NULL

CONSTRAINT [PK_IN_Contracts] PRIMARY KEY CLUSTERED

(

  [Snapshot_Date] ASC,

  [Contract_ID] ASC,

  [Attribute_Name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

And successfully converted to a concrete table like this:

InputTable:

SQL SELECT "Contract_ID", "Attribute_Name", "Attribute_Value_Text"

from CSI.dbo."IN_Contracts";

GenTable:

Generic Load Contract_ID, Attribute_Name,Attribute_Value_Text as AttributeValue Resident InputTable;

ResultTable:

LOAD Distinct Contract_ID Resident InputTable;

FOR i = 0 to NoOfTables()

  TableList:

  LOAD TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'GenTable.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

NEXT i

DROP TABLES TableList, InputTable;

Now I want to add the [Snapshot_Date] column, which is part of the key. How do I do that?

[#2]

No one?

[hic]

The Generic Load will work straight off with four fields. The two first will define the key, the third the attribute, and the fourth the value of the attribute.

You do not need the For-Next loops. I would remove them and just use the Generic Load. All the For-Next loop does, is join together a number of tables that don't need to be joined.

However, should you want a For-Next loop, you need to define the "ResultTable" differently. It should contain both fields that define the key.

HIC

[tmumaw]

Hi Henric,

I need some help with a generic load (first time trying it).  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;

•   16 Views    
•