topic Re: Data Model in QlikView in QlikView

Data Model in QlikView

Wed, 14 Oct 2015 13:31:10 GMT

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

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

Re: Data Model in QlikView

Wed, 14 Oct 2015 17:05:15 GMT

Any idea?

Maybe my questions are silly or I don't understand something what is crucial, but please give some advice.

Stefan

Re: Data Model in QlikView

crystles — Wed, 14 Oct 2015 17:17:15 GMT

Are var1 and var2 actual values? If they are, they do not need to be the title of the field. If you created titles for the fields then you could just use sum(var1) as var1 as a value.

But honestly I don't know if I fully understand the question or not. Hope this helps!

Re: Data Model in QlikView

swuehl — Wed, 14 Oct 2015 17:29:16 GMT

QV will do the join only when required by the aggregation. In you case, there is no join of the two tables required (only single field is used in aggregation), hence no duplication of val2 values for ID1.

Re: Data Model in QlikView

crystles — Wed, 14 Oct 2015 17:34:16 GMT

Can you post a picture of your data structure? So I can see how the tables are interacting?

Re: Data Model in QlikView

Wed, 14 Oct 2015 21:51:40 GMT

Thanks for response

Your answer and Henric's post give some explanation but in case I change table1 to:

table1

Id, Id2, val1

1, 1, 100

2, 2, 200

and make the chart (now join is required), i get:

Id2, sum(val2)

1, 100

2, 200

But I expect:

Id2, sum(val2)

1, 200

2, 200

because of generating all combinations before aggregation (e.g. in expression sum(val1*val2) result looks all right)

Where is my mistake?

Stefan

Thanks for help

Re: Data Model in QlikView

prabhu0505 — Wed, 14 Oct 2015 23:53:14 GMT

I dont know how far i understood..

=concat(aggr(only(Id)&' '&sum(val1),Id),Chr(10))

=concat(aggr(only(Id)&' '&sum(val2),Id),Chr(10))

Re: Data Model in QlikView

Anonymous — Thu, 15 Oct 2015 00:02:03 GMT

There is nothing wrong here. The statement "it doesn't behave like joined tables" is correct - the tables are not "joined", they are "linked", which is not exactly the same... As swuehl‌ pointed out, there is no duplication in this case.

Re: Data Model in QlikView

Digvijay_Singh — Thu, 15 Oct 2015 05:18:44 GMT

Just curious to know one related thing, how Qlikview internally store duplicate Id and its values, Is it aggregating just after load or keeps as it is? -

Table1

Id, val1

1, 100

Since it stores distinct value only in fields and only pointers in the table values, Will it store two pointer values for each 100 which is pointing to same ID value 1 or it aggregates value to 200 for Id 1 just after loading.

Not sure if I explained by query properly..

Re: Data Model in QlikView

jonathandienst — Thu, 15 Oct 2015 06:23:08 GMT

No QV can store duplicate values. The distinct values are in the symbol table, but the duplicate records can exist in the bit-stuffed pointers that represent the data table.

See Symbol Tables and Bit-Stuffed Pointers

Note that a table box only shows distinct values, but that does not mean that duplicate rows are not possible. A QV model is not a normal form database.

Re: Data Model in QlikView

Thu, 15 Oct 2015 07:07:33 GMT

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

Re: Data Model in QlikView

Thu, 15 Oct 2015 07:29:29 GMT

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

Re: Data Model in QlikView

swuehl — Thu, 15 Oct 2015 10:00:02 GMT

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.

Re: Data Model in QlikView

raajaswin — Thu, 15 Oct 2015 10:08:19 GMT

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!

Re: Data Model in QlikView

Anonymous — Thu, 15 Oct 2015 10:14:20 GMT

Hi stefan,

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

Re: Data Model in QlikView

Thu, 15 Oct 2015 12:58:06 GMT

"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

Re: Data Model in QlikView

Thu, 15 Oct 2015 13:04:21 GMT

Re: Data Model in QlikView

swuehl — Sat, 17 Oct 2015 14:28:56 GMT

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?

Re: Data Model in QlikView

Sun, 18 Oct 2015 12:19:39 GMT

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.