topic Re: exists clause in QlikView

exists clause

Tue, 03 Jul 2012 20:48:17 GMT

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.

Re: exists clause

cesaraccardi — Tue, 03 Jul 2012 20:59:13 GMT

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

Re: exists clause

Tue, 03 Jul 2012 21:02:58 GMT

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.

Re: exists clause

Thu, 05 Jul 2012 13:03:19 GMT

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

Re: exists clause

Thu, 05 Jul 2012 13:54:29 GMT

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.

Re: exists clause

Thu, 05 Jul 2012 15:04:43 GMT

thank you.it works!!

Re: exists clause

Thu, 05 Jul 2012 15:05:12 GMT

thank you