link of two tables of facts: circular loop

[#1]

Dear Friends,

I Modify my answer

I have 2 tables for budget

1. BUDGAGE: Year / Employee / Sales

2. BUDGET: date / Material group / Sales

I have a problem of circular loop. I can't solve the problem

Can you help me??

gennaro

[pover]

Rename the columns of the Invoice and Order table so that the names of the corresponding columns are the same and then concatenate the Invoice and Order tables into one large fact tables and add an additional column to identify the type of transaction per row. For example,

Load 'Order' as Transaction_Type,
O_Society as Society,
...
From Orders;

Concatenate

Load 'Invoice' as Transaction_Type,
I_Society as Society,
....
From Invoices;

Regards.

[#3]

I was hoping we could do differently!

thanks a lot

regards

[#4]

Hi friend,

Can I concatenate two tables if the structure is not equal?

in table ORDER there are OPEN_QTY and OPEN_VALUE that there aren't in table INVOICE

regards

[gauravkhare]

using qualify and unqualify will also help in eliminating the loop.

[gauravkhare]

Try this instead of concatenating perform a left/right join. It will be more beneficial rather than concatenating the tables as this help in taking only matching fields rather than fetching and joining both the tables.

[#7]

First concatenate the two table as suggested by Karl Pover and create one new table. Then use left join using any unque key in the main table for balance fields.

With regards,

Vineet Agarwal

[pover]

Yes, you could do a join or a table that's called a link table, but consider these points:

1. A concatenate function is faster then a join since a join has to evaluate which rows have corresponding values. Concatenation is even faster when all the columns are same, but there is no problem concatenating columns that aren't the same. It just fills the column with null in the other table.

2. Using concatenate takes advantage of QlikView's column-based compression that only saves only once a value that is repeated in the same column.

3. You can do some crazy data model, but a star model (ie.one fact table with multiple dimension tables) is easier to understand and reports are calculated faster with a star model than if you use a link table or any other complicated data model.

4. More time might be needed to develop the first concatenations at first, but it is easy to maintain.

5. Reload time will increase, but it should not increase by much.

6. Avoid creating 2 different columns (eg. o_customer, i_customer) with the same information that you have to link manually with an action in the interfase.

Regards.

[#9]

thanks Vineet

can you take me an example of left join for balance fields?

thanks

gennaro

[#10]

in this moment I have a beatiful star!

but I have a problem with date in table of budget

can you take me an example of a table with calendar??

thanks a lot!!

regards