topic 2 Fact tables: make it as One in QlikView

2 Fact tables: make it as One

dmohanty — Mon, 30 Dec 2013 13:23:41 GMT

Hi All,

The question is quite simple:

I have two transactional tables. On what basis I need to make it as one? Please suggest!

I meant to ask, what's the common scenario to look while converting all the Fact tables as a single table?

Below are my two table scripts:

TableA:

LOAD distinct
     SUPP_CD,
     SUPP_LOC,
     INV_CATEGORY,
     PART_NO,
     PART_CLR_CD,
     ORD_NO,
      KD_LOT_NO,
      DS_REV_SUM_QTY

FROM

(qvd);

TableB:

LOAD Distinct
     EXPEDITE_NUM as [ESR No],
     ORDER_NUM as ORD_NO,
      KD_LOT_NUM as KD_LOT_NO,
     REQUIRED_QUANTITY,
     ROUTE_QUANTITY,
     ARRIVAL_FLAG,
     ONLINE_DATE_TIME,
     EXTRACT_DATE_TIME ,

UPDATE_TS,
UPDATE_UID
FROM

(qvd);

Re: 2 Fact tables: make it as One

Gysbert_Wassenaar — Mon, 30 Dec 2013 13:31:37 GMT

I don't think it makes much sense to merge these two tables. But if you feel you must you could join them:

TableA:

load ... from TableA.qvd (qvd)

join

load ... from Table2.qvd (qvd)

Re: 2 Fact tables: make it as One

swuehl — Mon, 30 Dec 2013 13:32:37 GMT

You can concatenate the two fact tables, forcing concatenation using CONCATENATE LOAD prefix on the second load.

As far as I see, you will have two fields, ORD_NO and KD_LOT_NO, where selections made in will influence both sets of data.

Re: 2 Fact tables: make it as One

Mon, 30 Dec 2013 13:38:58 GMT

Hi Mohan,

In this scenario not promising merge two qvd files, there no common filed at all, if you want use JOIN, you get new table (fact). But number of column values is increased. And check with force to concatenate furthermore.

Re: 2 Fact tables: make it as One

dmohanty — Tue, 31 Dec 2013 07:17:29 GMT

Thank you friends for your suggestions.

But as a common practice, what would be your suggestions while dealing with such Fact Tables whre there no much relations and Forced Concatenation does not makes sense?

Re: 2 Fact tables: make it as One

Anonymous — Tue, 31 Dec 2013 07:37:33 GMT

approach for Star Schema model, ofcourse you can keep several seprate Fact tables seprate and can have

snow flake or relation schema model by linking them with primary key.

now a start schem model takes moderate use of RAM as well as CPU processor

while when you keep tables separate Qlikview bang RAM altough not much CPU cycle times.

so look for safe heavens you can join them as suggested by Gysbert which a natural join also

you can forced concatenate it but all in all it is better to left with fem seprate tables.

i feel you are getting it.

anant

Re: 2 Fact tables: make it as One

Tue, 31 Dec 2013 09:20:26 GMT

Hi DMohanty,

It clearly based on your requirement.Based on your previous two tables,it seems like you don't need to join.If you still want to have one fact table,join them based on ORD_NO as key.If you want to have all the fields and ORD_NO records are same in both Tables, you can ignore one from any table and concatenate them.

Thanks.