Help with join syntax

[Intuitive]

I have two intermediate data step tables that I need to join for the next step

The data in the previous steps is stored in a loaded  table as follows:

table_a

dim_a dim_b dim_c events

The intermediate tables: 

events_per_dim_c:
Load
dim_a,
dim_b
sum(events) / count(distinct dim_c) as avg_events_per_dim_c
Resident table_a
group by dim_a, dim_b
;

[events_by_dim_c]:
Load
dim_a,
dim_b,
dim_c
sum(events) as events_by_dim_c
resident table_a
group by dim_a, dim_b, dim_c
;

I tried:

table_b:
inner join (events_per_dim_c)

Load
avg_events_per_dim_c,
events_by_dim_c,
events_by_dim_c/ avg_events_per_dim_cas as events_rel_to_avg
resident [events_by_dim_c]

But I get an error, qlik does not recognize the field avg_events_per_dim_c

(Lets not discuss the use of synthetic keys here:) for now I just need the join to happen

[marcus_sommer]

Your join-load loads from events_by_dim_c but avg_events_per_dim_c comes from another table events_per_dim_c and therefore you get the load-error.

- Marcus

[AG-gugelbisolutions · Accepted solution]

Hi there, the reason why you are getting an error is that there is no avg_events_per_dim_c field in events_by_dim_c because you defined it in events_per_dim_c.

If I understand what you are trying to achive, and provided that joining events_by_dim_c  and events_per_dim_c by dim_a and dim_b is right, I would try something like this:

INNER JOIN (events_per_dim_c)
LOAD 
	dim_a, // K
	dim_b, // K
	dim_c
	events_by_dim_c
RESIDENT events_by_dim_c;

DROP TABLE events_by_dim_c;

TABLE_B:
Load
	avg_events_per_dim_c,
	events_by_dim_c,
	events_by_dim_c/ avg_events_per_dim_c as as events_rel_to_avg
resident [events_per_dim_c]

It might not be the most efficient piece of code but, at least, I hope it helps you understand.

[Intuitive]

Could you say more about the join syntax, I read the documentation on qlik site and seems I can't get it right

my code says  inner join (events_per_dim_c), so I understand that the join loads all the events_per_dim_c table and then loads the events_by_dim_c table in the next load expression. 

So I expect the load expression to have access to all the fields from both events_per_dim_c and events_by_dim_c

[AG-gugelbisolutions]

OK, no problem. I know it can be difficult to understand, in particular if you have SQL experience.

Let me recall the LOAD statement.

INNER JOIN (events_per_dim_c)
LOAD 
	dim_a, // K
	dim_b, // K
	dim_c
	events_by_dim_c
RESIDENT events_by_dim_c;

This statement loads four fields from the previously loaded events_by_dim_c table and then (inner) joins the result to events_per_dim_c using the common fields as join key, in this case dim_a and dim_b. There is no explicit join key definition; you guide the process by loading the right fields or renaming them. 

Only after this statement dim_c and events_by_dim_c fields are part of the events_per_dim_c  table and you can use them to build TABLE_B.

Finally, pay attention to the fact that events_by_dim_c still exists unchanged after the statement. This is why I dropped it.

[Intuitive]

---

@AG-gugelbisolutions wrote:

...in particular if you have SQL experience...

---

Thanx! Indeed, qlik had implemented it somewhat reverse to sql logic. However it is clear to me now, so the fields in the load statement are added to the table in the join statement.