topic Re: Full Outer Join in App Development

Full Outer Join

prabunakil — Fri, 02 Nov 2018 08:55:38 GMT

Hi i have a scenario as per below, two source of data need to map and show as per screen shoot.

Please advise how i can load the script.

Re: Full Outer Join

rubenmarin — Fri, 02 Nov 2018 09:00:33 GMT

Hi Prabu, that looks like concatenation, not a join:

TableName:

Load * From source1;

Concatenate LOAD * From Source2;

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/concatenate.htm

Re: Full Outer Join

prabunakil — Fri, 02 Nov 2018 09:07:27 GMT

But both source header row is different naming.

Re: Full Outer Join

niclaz79 — Fri, 02 Nov 2018 09:17:15 GMT

In your picture the column names are all the same, not sure what you mean.

Otherwise just rename the columns in your sources to be the same.

Re: Full Outer Join

prabunakil — Fri, 02 Nov 2018 09:21:01 GMT

Re: Full Outer Join

rubenmarin — Fri, 02 Nov 2018 09:23:48 GMT

You can use 'as' to remane column names:

LOAD Material,

Batch as [Combine Batch],

Qty

From Source1;

Concatenate

LOAD Material,

[3PL Batch] as [Combine Batch],

Qty

From Source2;

Re: Full Outer Join

niclaz79 — Fri, 02 Nov 2018 09:24:25 GMT

Source1:

LOAD

Batch as [Combined Batch]

FROM ....;

Concatenate(Source1)

LOAD

[3PL Batch] as [Combined Batch]

FROM ....;

Re: Full Outer Join

jobsonkjoseph — Fri, 02 Nov 2018 09:24:26 GMT

When tables with same field names are loaded, auto concatenation happens.

I think you are not sharing the correct table field names.

Re: Full Outer Join

prabunakil — Fri, 02 Nov 2018 09:24:51 GMT

sorry should be like this.

Re: Full Outer Join

jobsonkjoseph — Fri, 02 Nov 2018 09:52:59 GMT

Ruben reply is correct, it'll get the required result Prabhu.

Re: Full Outer Join

prabunakil — Fri, 02 Nov 2018 09:56:29 GMT

Hi Joseph

my above screenshoot i don think is a concatenate.

Re: Full Outer Join

prabunakil — Fri, 02 Nov 2018 09:57:13 GMT

Re: Full Outer Join

rubenmarin — Fri, 02 Nov 2018 10:03:51 GMT

Hi Prabu, when concatenating tables, fields with the same name are added to he previus ones, fields with new nemaes are created, giving Null() value to the row that comes from tables without that fields, so:

LOAD Material,

Batch as [Combine Batch],

Qty

From Source1;

Concatenate

LOAD Material,

[3PL Batch] as [Combine Batch],

[3PL Qty]

From Source2;

Re: Full Outer Join

jobsonkjoseph — Fri, 02 Nov 2018 10:13:59 GMT

Dear Prabu,

This will work,

tab1:

LOAD Material,

Batch as [Combine Batch],

Qty

FROM

Community\join\Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Join

tab2:

LOAD Material,

[3PL Batch] as [Combine Batch],

[3PL Qty]

FROM

Community\join\Book1.xlsx

(ooxml, embedded labels, table is Sheet2);

Re: Full Outer Join

vitaliichupryna — Fri, 02 Nov 2018 10:17:23 GMT

Hi Prabu,

See example in attachment.

Concatenate works fine, but you need to add

0 AS [3PL Qty] to the first source table and

0 AS [Qty] to the second table

Thanks,

Vitalii

Re: Full Outer Join

prabunakil — Fri, 02 Nov 2018 10:51:56 GMT

is working now., thank you very much/