topic Re: Circular Reference-Actual and Target fact tables in QlikView

Circular Reference-Actual and Target fact tables

Sun, 03 Jul 2016 10:11:54 GMT

Hello! my question is about circular references in loading script . I have 2 fact table from excel (Actual and target) and 1 dim table from sql, by loading data the circular reference error appear and table viewer shows such diagram:

i have seen posts about circular references but it is not effective for me on this model . i need simple data model to solve this problem .

Thanks in advance for any help on this!

Re: Circular Reference-Actual and Target fact tables

marcus_sommer — Sun, 03 Jul 2016 16:11:15 GMT

I would very probably concatenate the target- and actual-tables and adjust the YearMonth from the target to a date with something like:

makedate(Year, Month, 1) as Date8

you might need some further functions to extract year and month from the YearMonth field.

- Marcus

Re: Circular Reference-Actual and Target fact tables

Anonymous — Mon, 04 Jul 2016 02:11:44 GMT

Create a link table by pulling out Date8 field from DimTime & Actual , OrgID field from Target & Actual table .

Then drop these fields by dropping from original table .

Or you can rename the fileds .

Re: Circular Reference-Actual and Target fact tables

marjan_it — Mon, 04 Jul 2016 04:08:32 GMT

you can use left join in order to your basic fact in your script and then loop error will be solved!

Re: Circular Reference-Actual and Target fact tables

Mon, 04 Jul 2016 04:14:07 GMT

My dummy method: Rename the columns in two of your three tables, and they will not link automatically! Hope this helps.

Re: Circular Reference-Actual and Target fact tables

jansen28 — Mon, 04 Jul 2016 04:29:14 GMT

I would probably choose to concatenate both fact tables considering the volume. And use date function to convert Yearmonth. You could also create a flag to differentiate Actual Vs Target.

Re: Circular Reference-Actual and Target fact tables

Mon, 04 Jul 2016 05:45:45 GMT

Thanks for the prompt reply .

I think this approach will work , but i am really confused in this case . can you help me about writing link table script.

Thanks in advance,

masood

Re: Circular Reference-Actual and Target fact tables

Anonymous — Mon, 04 Jul 2016 07:25:13 GMT

Please see the Script ,

DimTime:

LOAD YearMonth,

Date8,

CalenderQuarter,

CalenderYear

FROM

(ooxml, embedded labels, table is Sheet1);

Target:

LOAD OrgID,

YearMonth,

OrgName,

TargetAmount

FROM

(ooxml, embedded labels, table is Sheet2);

Actual:

LOAD Date8 as Date_8,

OrgID,

Amount

FROM

(ooxml, embedded labels, table is Sheet3);

LinkTable:

load YearMonth as YearMonth,

Date8 as Date8

Resident DimTime;

Concatenate

LOAD OrgID

Resident Target;

drop Field YearMonth from DimTime ;

drop Field OrgID from Target;

Hoping that your Fields are distinct & common between the tables .