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]

Any idea?

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

Stefan

[crystles]

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!

[swuehl]

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.

See also Henric's explanations in

The Calculation Engine

[crystles]

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

[#6]

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

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

[prabhu0505]

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))

[Anonymous]

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.

[Digvijay_Singh]

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

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..

[jonathandienst]

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.