Data Model in QlikView

[#1]

Hi,

I have a problem with QlikView .

Looking at associations of two tables in TableViewer I can treat them as they were joined.

But when I want to (kind of SQL)  select (in chart)     dimension from one table and sum(measure) from second table, it doesn't behave like joined tables.

Example:

table1

Id, val1

1, 100

1, 100

2, 200

table2

Id, val2

1, 100

2, 200

in chart I would get 

Id, sum(val1), sum(val2)

1, 200, 100

2, 200, 200

I would expect to get column sum(val1)=sum(val2)

Two tables isn't a big problem , but when I meet a few tables associated together and need to calculate something that is not included in one table....

My questions:

How do you deal with such situation (without changing data model)?

How do you recognize (looking at tables' relations) what is possible to calculate or what produces false numbers? Or better: how do you recognize what to expect?

Is there any name for such behaviour of QlikView (associative model?)?

Stefan

[#2]

You understand correctly but this case was explained by swuehl - no need of join - summing as it is in the table.

But look at the second case (my third post) where id is changed by Id2. Then there is a need of join (all combinations) but result is the same.

Stefan

[#3]

Michael,

Look at the second case - my third post. Do you agree that there should be a combination of values - fields from different tables - and aggregation should give different result? Could you explain this case?

..."linked" , which is not exactly the same...    this is the point I try to understand    What does it mean? What are the rules? How does it work with chart?

Stefan

[swuehl]

Stefan, I don't think the join is required in your new data set for calculating the aggregation (still only val2 is aggregated).

QV still will group by ID2 resp ID,

If you change your table however to

table1

Id, Id2, val1

1, 1, 100

1, 3, 100

2, 2, 200

Then use Id2 as dimension, and sum(val2) as expression, you will see that each single aggregation is not duplicating val2, neither do the expression total, but the sum-of-rows.

[raajaswin]

Hi,

In Qlikview there is a lot of difference between join and association(link).

Join:

If you join two table with key word JOIN, INNER JOIn etc. It will get joined and in Table viewer you will get one table only

Link:

If you have two column names same in two table and you did not use key word JOIN. It is linked (We may assume like OUTER JOIN). In Table viewer we will get Two tables with all the rows..

Hope it helps!

[Anonymous]

Hi stefan,

Please Can you post Screenshot of your data model from table viewer?

[#7]

"I don't think the join is required in your new data set for calculating the aggregation (still only val2 is aggregated).

QV still will group by ID2 resp ID, "

Sorry, but I don't understand:

two fields (Id2, val2) from different tables. I group by Id2 and sum val2. They must be joined somehow by Id to get result in chart. I agree that aggregation (summing) is made only in one table but grouping field is in another, so join is needed.

I think that join/link is necessary - how chart would show the results otherwise? 

I try to compare QV result to SQL result.

In SQL: select Id2, sum(val2) as sum_val2 from Table1, Table2 where  Table1.Id= Table2.Id  

and result is as I expect.

After changing table, as you wrote, result is ok (the same as SQL ) ... but if you add another row 1,3,100

well I think field values aren't the issue here. 

thank for response

Stefan

[#8]

[swuehl]

Michael,

Look at the second case - my third post. Do you agree that there should be a combination of values - fields from different tables - and aggregation should give different result? Could you explain this case?

..."linked" , which is not exactly the same...    this is the point I try to understand    What does it mean? What are the rules? How does it work with chart?

Stefan

I think your confusion is caused by the difference between QV's associative data model compared to a join model, like a classic DBMS.

The link between to tables is used in two ways:

Used in the logical inference engine, it is used as path to determine the possible symbol table values and table records:

Logical Inference and Aggregations

When it comes to aggregations, a link is used as possible join path, i.e. it defines the join between tables when fields from these tables are used together in an aggregation.

But as long as the aggregation scope is limited to a single table in the data model, there is no need for that join. The join is performed only when needed, on demand.

A common example is having two tables, a table with order headers and order lines. The header will contain a header fact, like a freight cost value, and the lines cost of each line.

Using a dimension like Customer or OrderID, QV will calculate Sum(Freight) and Sum(ItemCost) both in the scope of their tables, not performing a JOIN, thus not duplicating the freight cost per order line.

Does this makes things somewhat clearer?

[#10]

I agree that my confusion comes from comparison of QV associative data model nad classic DBMS.

I also was going direction you described - the join is performed when needed. And example you showed is clear.

But, could you explain the case I wrote? I mean:

table1 ( Id, Id2, val1)

table2 ( Id, val2)

with values as earlier

chart :

Id2, sum(val1), sum(val2)

I think that there is a need of  "real" join here but it doesen't happen.  val2 fom table2  must be "connected" with Id2 from table1.

Result I get suggests that (in one row of chart) the engine takes the value from Id2, "makes a selection", checks what values from Id (in table2) are possible (distinct), and sums val2 for them, finally gives back result to Id2.

When we  sum(val1*val2), the engine behaves like classic DBMS. The difference is that sum involves fields from different tables.

So, maybe, it si possible to sum up the rules in chart calculation:

1)  if join isn't needed - QV doesn't do it

2)  if join is needed and aggregation function involves field from one table - QV behaves in "associative" way

3)  if join is needed and aggregation function involves fields from both tables - QV behaves like classic DBMS

Well, I'm not 100% sure. What do you think?

Many thanks for your help.

S