topic Re: Concatenation Multiple fact tables different dimensions in QlikView

Concatenation Multiple fact tables different dimensions

ivandrago — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

At the moment I have three fact tables where two of the three would have some kind of relationship i.e the same CustomerID & DateEntered, I would also have a field called [Record Type] which would differentiate what each fact table is as per below:

Fact:

Load

'Sales' as [Record Type]

,OrderId as [PK]

,CustomerID

,DateEntered

,InvoiceDate

,Product

,Amount as Amount

From [..\Sales.qvd] (QVD);

CONCATENATE

Load

'Quotes' as [Record Type]

,QuoteId as [PK]

,CustomerID

,DateEntered

,LongDescription

,QuoteAmount as Amount

From [..\Quotes.qvd] (QVD);

CONCATENATE

Load

'Schedules' as [Record Type]

,SchedueId &'_'&SchedueVisitNo as [PK]

,PlaceId

,ScheduleDateEntered as DateEntered

,SchduleName as EngineerName

From [..\Schdeules.qvd] (QVD)

The Sales and Quotes QVD's come from the same ERP system, the Schedules QVD is coming from a different Application altogether and has no relationship, I know this is a case of a Data Island issue but the end user wants it to be all on one Document.

My question is, should I have the same amount of field names on each QVD and put a default value i.e 'UNKNOWN' if it is a Text Field and 0 if it value? Or does it matter if there are more fields on one qvd than the other qvd when CONCATENATE the qvd's together?

Thanks

Re: Concatenation Multiple fact tables different dimensions

devarasu07 — Mon, 10 Jul 2017 01:58:02 GMT

Hi,

It's not required to keep same amount of field names in each qvd.

You may try like below, is (SchedueId & '|' & SchedueVisitNo = CustomerID ?)

Fact:

Load

'Sales' as [Record Type],

OrderId as [PK],

CustomerID,

DateEntered,

InvoiceDate,

Product,

CustomerID & '|' & Date(DateEntered) & '|' & 'Sales' as CustomerID_DateEntered_Type,

Amount as Amount

From [..\Sales.qvd] (QVD);

CONCATENATE (Fact)

Load

'Quotes' as [Record Type],

QuoteId as [PK],

CustomerID,

DateEntered,

LongDescription,

CustomerID & '|' & Date(DateEntered) & '|' & 'Quotes' as CustomerID_DateEntered_Type,

QuoteAmount as Amount

From [..\Quotes.qvd] (QVD);

CONCATENATE (Fact)

Load Schedules' as [Record Type],

SchedueId &'_'&SchedueVisitNo as [PK],

PlaceId,

ScheduleDateEntered as DateEntered,

SchedueId & '|' & SchedueVisitNo & '|' & Date(Floor(ScheduleDateEntered)) & '|' & 'Schedules' as CustomerID_DateEntered_Type,

SchduleName as EngineerName

From [..\Schdeules.qvd] (QVD)

Thanks,

Deva

Re: Concatenation Multiple fact tables different dimensions

jonathandienst — Mon, 10 Jul 2017 05:04:35 GMT

It is not necessary to have the same field names in all the LOAD statements. For each load, the missing field will be automatically loaded with null values. This normal for a fact table that contains more than one type of value or varying granularity levels (eg budgets values and actual values).