topic Re: Load if not equal in QlikView

Load if not equal

qlikviewalex — Thu, 21 Jul 2016 06:51:33 GMT

Hello,

I have a problem with my Skripting.

I join two Tables to one.

But I want only the elements, where the [Object-ID] is not included in Table1

Aufgaben:

LOAD [Object-ID],

FROM

[..\10_Data\test.xlsx]

(ooxml, embedded labels, table is Aufgaben);

join load distinct

[Object-ID],

FROM

[..\30_QVDs\Aufgaben.qvd] where ...............

(qvd);

I hope someone can help me.

Alex

Re: Load if not equal

oknotsen — Thu, 21 Jul 2016 06:55:55 GMT

Add this line of code:

where not exists([Object-ID]

to the 2nd load statement.

So the 2nd load statement turns into this:

join load distinct

[Object-ID],

FROM

[..\30_QVDs\Aufgaben.qvd]

where not exists([Object-ID]

(qvd);

Re: Load if not equal

qlikviewalex — Thu, 21 Jul 2016 07:06:58 GMT

Unfortunately it does not work:

Script Error:

Error opening file '..\30_QVDs\Aufgaben.qvd where not exists [Object-ID' The system cannot find the file specified.

join load distinct

[Object-ID],

FROM

[..\30_QVDs\Aufgaben.qvd] where not exists [Object-ID]

(qvd);

Re: Load if not equal

Digvijay_Singh — Thu, 21 Jul 2016 07:19:11 GMT

Are you sure you need join, all your field names in both the tables are same, you may want to use concatenate.

Re: Load if not equal

susovan — Thu, 21 Jul 2016 07:25:45 GMT

Hi,

You can also try this script,

Table1:

LOAD [Object-ID],

FROM

[..\10_Data\test.xlsx]

(ooxml, embedded labels, table is Aufgaben);

Concatenate

Table2:

LOAD [Object-ID],

FROM

[..\30_QVDs\Aufgaben.qvd] where ...............

(qvd);

Re: Load if not equal

avinashelite — Thu, 21 Jul 2016 07:26:36 GMT

Try like this

Aufgaben:

LOAD [Object-ID],

FROM

[..\10_Data\test.xlsx]

(ooxml, embedded labels, table is Aufgaben);

[Object-ID],

FROM

[..\30_QVDs\Aufgaben.qvd]

where not exists([Object-ID];

// since both the tables have similar columns it will automatically gets concatenated

Re: Load if not equal

qlikviewalex — Thu, 21 Jul 2016 07:41:16 GMT

Thanks. You are all right. But my problem is not to connect the two tables.

I like only the elements where the Object-ID is NOT in the first Table.

Example:

Table1

Object-ID	a	b
1	a	a
2	b	b
3	c	c
4	d	d
5	e	e

Table2

Object-ID	a	b
2	x	y
7	g	g
1	a	ttt

the result should look like this:

Object-ID	a	b
1	a	a
2	b	b
3	c	c
4	d	d
5	e	e
7	g	g

Re: Load if not equal

oknotsen — Thu, 21 Jul 2016 07:45:22 GMT

join load distinct

[Object-ID],

FROM

[..\30_QVDs\Aufgaben.qvd]

(qvd)

where not exists([Object-ID])

;

Re: Load if not equal

avinashelite — Thu, 21 Jul 2016 07:45:58 GMT

Try this script it will work

Aufgaben:

LOAD [Object-ID],

FROM

Table1

(ooxml, embedded labels, table is Aufgaben);

[Object-ID],

FROM

table2

where not exists([Object-ID];

Re: Load if not equal

oknotsen — Thu, 21 Jul 2016 07:46:39 GMT

Question:

Do you want to JOIN or CONCATENATE?

Re: Load if not equal

qlikviewalex — Thu, 21 Jul 2016 07:55:51 GMT

Thanks. It works.

The mistake was the (qvd);

That's wrong:

[..\30_QVDs\Aufgaben.qvd]

where not exists([Object-ID]

(qvd);

That's work:

[..\30_QVDs\Aufgaben.qvd](qvd) where not exists( [Object-ID]);