topic Re: Help with join tables with exclusion data. in QlikView

Help with join tables with exclusion data.

Mon, 07 Apr 2014 13:13:06 GMT

Hi,

I need join two tables, similar to outer join, but I need add into the first table only values of the second table that not exist on first table. Validating only the key field.

How is that done..

Re: Help with join tables with exclusion data.

peschu123 — Mon, 07 Apr 2014 13:18:49 GMT

Hi,

a few more data or example files would be great...

But take a look at exists() function

You can use it like

LOAD ...

FROM/RESIDENT ... WHERE NOT EXISTS(key);

Re: Help with join tables with exclusion data.

yduval75 — Mon, 07 Apr 2014 13:23:55 GMT

You have to use the concatenate function in addition to not exits function as said peshu82

If you have the same fields and the same number of fields between table 1 and table 2, the concatenation is automatic.

Concatenate (Table1)

LOAD

----------

From Table2

where not exists (key);

Re: Help with join tables with exclusion data.

Mon, 07 Apr 2014 14:18:57 GMT

With Not Exists() Function: Show error Field not found <Key>

The name of the fields is the same on the tables 1 and 2.

Where I am going wrong.

Example:

Table 1

Key, A

1,10

2,10

3,10

Table 2

Key, A

1,9

4,9

Expected Result

Key, A

1,10

2,10

3,10

4,9

Result With Outer Join OR Concatenate

Key, A

1,10

1,9

2,10

3,10

4,9

I use this code.

[Table1]:

LOAD

Key,

FROM

Table1.QVD;

[Table2]:

Concatenate(Table1)

LOAD

Key,

FROM

Table2.QVD

WHERE

NOT EXISTS(Key);

Re: Re: Help with join tables with exclusion data.

yduval75 — Mon, 07 Apr 2014 14:30:48 GMT

I tried with your example.

I get the expected result

I have attached my qvw

Re: Help with join tables with exclusion data.

Mon, 07 Apr 2014 14:43:58 GMT

Yoann, look my real code, but don't worked.

Will be the name of field. I am renaming all fields.

[Preco_Medio_Venda_Tmp]:

LOAD

left([Faturamento - Código Produto],5) & ' - ' & [Faturamento - Mercado] AS CHAVE,

left([Faturamento - Código Produto],5) AS [Preço Médio Venda - Referencia],

[Faturamento - Mercado] AS [Preço Médio Venda - Mercado],

Sum([Faturamento - Valor Líquido Total] - [Faturamento - Valor ICMS Substituição] - [Faturamento - Valor IPI])/Sum([Faturamento - Quantidade]) AS [Preço Médio Venda - Valor Venda Médio]

RESIDENT

[Fato Faturamento]

WHERE

[Faturamento - Ano] = $(ANO_BASE)

AND Num(Month([Faturamento - Data Emissão])) = Num($(MES_BASE))

AND [Faturamento - Grupo CFOP] = 'VENDA'

GROUP BY

left([Faturamento - Código Produto],5),

[Faturamento - Mercado];

LOAD

left([Faturamento - Código Produto],5) & ' - ' & [Faturamento - Mercado] AS CHAVE,

left([Faturamento - Código Produto],5) AS [Preço Médio Venda - Referencia],

[Faturamento - Mercado] AS [Preço Médio Venda - Mercado],

Sum([Faturamento - Valor Líquido Total] - [Faturamento - Valor ICMS Substituição] - [Faturamento - Valor IPI])/Sum([Faturamento - Quantidade]) AS [Preço Médio Venda - Valor Venda Médio]

RESIDENT

[Fato Faturamento]

WHERE

NOT EXISTS(CHAVE)

AND [Faturamento - Ano] = $(ANO_BASE)

AND Num(Month([Faturamento - Data Emissão])) = Num($(MES_BASE) - 1)

AND [Faturamento - Grupo CFOP] = 'VENDA'

GROUP BY

left([Faturamento - Código Produto],5),

[Faturamento - Mercado];

Re: Help with join tables with exclusion data.

yduval75 — Mon, 07 Apr 2014 14:49:35 GMT

You have to use

NOT EXISTS (CHAVE, left([Faturamento - Código Produto],5) & ' - ' & [Faturamento - Mercado])

instead of

NOT EXISTS (CHAVE)

Re: Help with join tables with exclusion data.

Mon, 07 Apr 2014 15:08:18 GMT

Now it's worked, but don't load data of the second table.

Re: Help with join tables with exclusion data.

yduval75 — Mon, 07 Apr 2014 15:16:14 GMT

For the test, just load the second table and check that you have different values

Re: Help with join tables with exclusion data.

Mon, 07 Apr 2014 16:34:15 GMT

Yoann,

Worked perfectly now, I was loaded null values on the first table.

Thanks for the help.