Join FACT table with Direct Query table

Sun, 06 Sep 2015 17:16:05 GMT

Hi all,

Can i join a normal in memory fact table with a DIRECT query table? Of course i will not une any synthetic keys.

What i am trying to achieve is have a grouped table (monthly grain) and a DIRECT query table for the details.

this is the code i am using

Right now it is not working. (direct query failed, and i see nothing in the DB using profiler).

det:

load *,autonumber(SalesPersonID & CustomerID & OrderMonth & OrderYear) as grpID2

;

DIRECT QUERY

DIMENSION

CustomerID,

SalesPersonID,

SalesOrderID,

OrderDate,

NATIVE('month([OrderDate])') as OrderMonth,

NATIVE('Year([OrderDate])') as OrderYear

MEASURE

SubTotal,

TaxAmt,

TotalDue

Detail

DueDate,

ShipDate,

AccountNumber,

CreditCardApprovalCode,

rowguid,

ModifiedDate

FROM [Sales].[SalesOrderHeader];

grp:

select

month([OrderDate]) as OrderMonth,

Year([OrderDate]) as OrderYear,

CustomerID,

SalesPersonID,

sum(SubTotal) as SubTotalGrouped,

sum(TaxAmt) as TaxAmtGrouped,

sum(TotalDue) as TotalDueGrouped

FROM [Sales].[SalesOrderHeader]

group by

month([OrderDate]),

Year([OrderDate]),

CustomerID,

SalesPersonID;

// intermediate table to join facts

cnt:

Load distinct

CustomerID,

OrderMonth,

OrderYear,

SalesPersonID,

autonumber(SalesPersonID & CustomerID & OrderMonth & OrderYear) as grpID

resident grp;

concatenate

Load distinct

CustomerID,

OrderMonth,

OrderYear,

SalesPersonID,

autonumber(SalesPersonID & CustomerID & OrderMonth & OrderYear) as grpID2

resident det;

drop field CustomerID from det;

drop field OrderMonth from det;

drop field OrderYear from det;

drop field SalesPersonID from det;

grp2:

Load

autonumber(SalesPersonID &CustomerID & OrderMonth & OrderYear) as grpID,

SubTotalGrouped,

TaxAmtGrouped,

TotalDueGrouped

resident grp;

drop table grp;

Re: Join FACT table with Direct Query table

robert_mika — Fri, 16 Oct 2015 08:37:06 GMT

Did you establish your connection to ODBC?

It works for me

Re: Join FACT table with Direct Query table

sujeetsingh — Fri, 16 Oct 2015 08:53:41 GMT

Need a sample please

Re: Join FACT table with Direct Query table

robert_mika — Mon, 19 Oct 2015 07:24:16 GMT

I think the sample is the AdvetureWorks script provided..

topic Join FACT table with Direct Query table in QlikView

Join FACT table with Direct Query table

Re: Join FACT table with Direct Query table

Re: Join FACT table with Direct Query table

Re: Join FACT table with Direct Query table