Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Joins and data model

Hi,

I'm new on QlikView and I'd like to have some advices for a particular problem :

- I created a dashboard which presents 5 indicators. This indicators works fine and use 6 tables defined in the data model. The tables are joined with inner joins. I want to build a last indicator which uses only 2 of the 6 tables. The problem is only for this indicator I don't want any supplementary join that the join between the 2 tables. If the others joins are applied the result is "too small".

I can't create this indicator on a different dashboard and as I said, The others are good so the model seems too.

Any explication or advice are welcome and tell me if some points need precisions.

Thanks

8 Replies
Not applicable
Author

Hi,

A quick and dirty way of doing this without getting too complicated, and if the data is not too large would be to create 2 new tables from whats already in memory doing resident loads and using the qualify and unqualify commands.

Newtable1:

Qualify *;

Unqualify NewKeyField;

Load

*,

oldkeyfield as NewKeyField;

resident oldtable1;

unqualify *;

and replicate for the second table.

You should end up with two data islands. Your original and new qualified island with a single unqualified join on the NewKeyField.

There are many ways of doing this....you may also want to look at Set Analysis,

Regards,

Neil

Not applicable
Author

Thanks a lot for your response.

If I build two "islands" of data how does it work in memory? I will have two sets of data really separated or does the system generates a unique data set with all possible combinations?

For the "Set Analysis" solution, I looked but I own I don't know how to apply this to my problem. I understand the concept but I'm incompetent to build it.

Thanks

Not applicable
Author

As I said it would be quick and dirty,

When doing the resident loads, I showed in the stript load *, Keyfield. You could specify only the fields you need to cut down on memory.

The original suggested solution would take double the memory because you are loading the data in twice. It just depends on how much data you are loading. At moment how much memory does the qvw occupy when loaded into developer you can check it in taskmanager.

Regards,

Neil

Not applicable
Author

Ok I thank you for your time.

For my cultivation, in few words what could be the clean way to do this?

Smile

Not applicable
Author

what formula or sums are you trying to do between these 2 tables and i might be able to make some suggestions of how to do it.

Regards,

Neil

Not applicable
Author

The formula is really simply. It's just a division between 2 sum. To my mind It's "only" a data problem and a join problem in particular.

The others joins limits the records of the 2 tables and this causes wrongs sums (wrong because of number of rows). For example if only the 2 tables were joined, we would have 1 000 rows but because of others joins we have 500 rows. So the sums aren't correct...

Thank you for your help

Not applicable
Author

You may have to use Set analysis a very simple example below

Here is an example for doing total gross profit

SUM( {1} Margin) / SUM( {1}Sales)

{1} is the set operator and denotes a full set of data.

This will sum all the margin regardless of selection and divide by the sum of all sales regardless of selection.

You may also need to incorporate If Statements eg

SUM( {1} IF(Category = 'Shoes', Margin) ) / SUM( {1} IF(Category = 'Shoes', Sales) )

To give total Gross profit for shoes,

Regards,

Neil

Not applicable
Author

Ok thank a lot for your response I understand the principle.

To take your example, in my case, Margin comes from Table1 and Sales from Table2.

Does set analysis applies to my problem? If I use "{1}" do I have all Margin and all Sales related or do I have all Margin and all Sales like a full outer join?

Tell me if my question needs more explanations.

Thanks