topic Re: Load generic table with more than one column as key in QlikView

Load generic table with more than one column as key

Mon, 03 Feb 2014 12:32:48 GMT

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?

Re: Load generic table with more than one column as key

Mon, 03 Feb 2014 14:46:43 GMT

No one?

Re: Load generic table with more than one column as key

hic — Mon, 03 Feb 2014 16:34:33 GMT

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

Re: Load generic table with more than one column as key

tmumaw — Thu, 04 Oct 2018 17:34:05 GMT

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