topic Full Outer join two table in Search the Community

Full Outer join two table

Magalytobel — Fri, 09 Apr 2021 01:36:10 GMT

Please help me solve this puzzle.

I have two different tables. I want to use one table as the lead one and the other one as the dimension table. I want all records from one table and the data from the other table if there is a match otherwise it shows blanc.

table 1	table 2
	aa
xx
yy	yy
zz

if I have 100 records in field YY table 1 after joining with table 2 with the field yy I still want the 100 records.

Re: Full Outer join two table

marcus_sommer — Mon, 12 Apr 2021 09:03:34 GMT

In this case you need a left or right join, like:

t: load YY from Table1; left join(t) load yy as YY from Table2;

but by joins in general it depends on the existing data-structure and the data-quality if it will create duplicate records or if it may remove ones. It's not controllable with the join-statement itself - else you will need various steps in beforehand or afterwards to check and control the data.

If you couldn't ensure it with rather simply measures you shouldn't use joins else you should look for other approaches - quite common for such use-cases in Qlik is mapping.

- Marcus

Re: Full Outer join two table

MayilVahanan — Mon, 12 Apr 2021 09:32:37 GMT

HI @Magalytobel

You can try with left keep concept.

Table1:

load YY from Table1;

left Keep(Table1)

load YY from Table2;

In that case, final output in table viewer, you can able to see 2 table,

but its load the info in Table1 alone in Table 2. Hope it helps