Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

exists clause

have one base table A  with fields upc ,description.

want to load data from 6 more tables into the  table A but only those rows where UPC does not exist in  table A

example

table A

UPC,description

123,asd

124,der

129,dsa

table B

UPC,description

123,asd

333,sss

129,dsa

table C

UPC,description

123,asd

999,QQQ

129,dsa

thanks in advance.

1 Solution

Accepted Solutions
Not applicable

Re: exists clause

ItemTable:

LOAD

UPC,

Description;

SQL

SELECT

       UPC,

      Description

  FROM itemmain ;

LOAD

UPC,

Description;

SQL

SELECT

       UPC,

      Description

  FROM GFIMAIN where not exists(UPC) ;

its not working.Am i missing something ?

thank you

6 Replies
cesaraccardi
Valued Contributor

Re: exists clause

Hi,

First you load the table A, for the next loads you do this way:

table B
load * from (...) where not exists(UPC);

table C
load * from (...) where not exists(UPC);

Regards,

Cesar

Not applicable

Re: exists clause

The other 5 tables seem to be in the DB or excel files. If it was inside the qvw, they would merge them automatically.

If they are inside other files you could simply use where (like a regular SQL)

LOAD *

FROM TABLEA;

LOAD *

FROM TABLE B

WHERE NOT EXISTS(UPC);

LOAD *

FROM TABLE C

WHERE NOT EXISTS(UPC);

and so on.

Not applicable

Re: exists clause

ItemTable:

LOAD

UPC,

Description;

SQL

SELECT

       UPC,

      Description

  FROM itemmain ;

LOAD

UPC,

Description;

SQL

SELECT

       UPC,

      Description

  FROM GFIMAIN where not exists(UPC) ;

its not working.Am i missing something ?

thank you

Not applicable

Re: exists clause

YES.

You are mixing SQL with Qlikview’s internal load.

LOAD

UPC,

Description;

SQL

SELECT

UPC,

Description

FROM itemmain ;

// the where should be after all reg where brought by the SQL.

LOAD

UPC,

Description

where not exists(UPC) ;

SQL

SELECT

UPC,

Description

FROM GFIMAIN;

Esta mensagem (incluíndo qualquer anexo) é dirigida apenas para o uso do indivíduo ou da entidade a qual está endereçada e pode conter informações privadas, proprietárias, privilegiadas, confidenciais que podem servir como evidências sob as leis aplicáveis ou em processos judiciais.

Caso você não seja o destinatário pretendido, você está aqui notificado que qualquer uso, disseminação, distribuição, ou cópia dessa comunicação é estritamente proibida. Se você recebeu essa comunicação por engano, notifique-nos imediatamente por telefone, e destrua essa mensagem se estiver impressa ou (ii) exclua imediatamente essa mensagem se esta for uma comunicação eletrônica.

Obrigado.

This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product.

If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and destroy this message if this is printed or (ii) delete this message immediately if this is an electronic communication.

Thank you.

Not applicable

Re: exists clause

thank you.it works!!

Not applicable

Re: exists clause

thank you

Community Browser