topic Re: Concatenate in QlikView

Concatenate

Wed, 18 Mar 2015 05:58:25 GMT

I have the following tables:

Entry No_	Item No_	Quantity	Entry Type	Document No_
3248633	224964	-100	5	RC-32538

Table2:

Entry No_	Item No_	Quantity	Entry Type	Document No_
32486300	1	-9	5	RC-32538
32486319	2	-9	5	RC-32538
19	6	-9	5	RC-32539
38	7	-9	5	RC-32539

I want to concatenate Table 2 with Table 1.

but where document No.s are not the same.

Since RC-32538 already appeared in the first table, only the ;last two records should be concatenated with the first table.

The result table should be as follows:

Entry No_	Item No_	Quantity	Entry Type	Document No_
3248633	224964	-100	5	RC-32538
19	6	-9	5	RC-32539
38	7	-9	5	RC-32539

Can anyone help me with a possible solution.

Re: Concatenate

Wed, 18 Mar 2015 06:04:22 GMT

Hi,

LOAD *

from 1;

concatenate

load *

from 2 where not exits(Document No_);

Look attached file

Regards

Vimlesh

Re: Concatenate

Wed, 18 Mar 2015 06:14:53 GMT

But it compares fields in the second table also.

For eg. i want both the lines for the document no. RC-32539 to be concatenated.

only RC-32538 should not be concatenated.

but it is adding only first line.

Second line is ignored.

Re: Concatenate

jonathandienst — Wed, 18 Mar 2015 06:22:46 GMT

What criterion are you using to decide what should be concatenated?

Jonathan

Re: Concatenate

anbu1984 — Wed, 18 Mar 2015 06:23:11 GMT

Table1:

Load * Inline [

Entry No_,Item No_,Quantity,Entry Type,Document No_

3248633,224964,-100,5,RC-32538 ];

Table2:

Load [Entry No_],[Item No_],Quantity,[Entry Type],[Document No1_] As [Document No_];

Load * Inline [

Entry No_,Item No_,Quantity,Entry Type,Document No1_

32486300,1,-9,5,RC-32538

32486319,2,-9,5,RC-32538

19,6,-9,5,RC-32539

38,7,-9,5,RC-32539 ]

Where

Not Exists([Document No_],[Document No1_]);

Re: Concatenate

anbu1984 — Wed, 18 Mar 2015 06:27:00 GMT

This will remove duplicates based on "Document No_" from second table.

load *

from 2 where not exits(Document No_);

Re: Concatenate

Wed, 18 Mar 2015 06:29:36 GMT

Not getting desired results.

check the following.

RC:

LOAD [Entry No_],

[Item No_],

Quantity,

[Entry Type],

[Document No_]

FROM

RC.xlsx

(ooxml, embedded labels, table is Sheet1);

RC1:

LOAD [Entry No_],

[Item No_],

Quantity,

[Entry Type],

[Document No_] as Doc

FROM

RC.xlsx

(ooxml, embedded labels, table is Sheet2)

where not exists ([Document No_]),(Doc);

Re: Concatenate

PrashantSangle — Wed, 18 Mar 2015 06:35:46 GMT

Hi,

Modify your script like below

RC:

LOAD [Entry No_],

[Item No_],

Quantity,

[Entry Type],

[Document No_]

FROM

RC.xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD [Entry No_],

[Item No_],

Quantity,

[Entry Type],

[Document No_] as Doc

FROM

RC.xlsx

(ooxml, embedded labels, table is Sheet2)

where not exists ([Document No_],[Document No_]);

Regards

Re: Concatenate

anbu1984 — Wed, 18 Mar 2015 06:37:36 GMT

Inline table is working for me. Can you post RC.xlsx file

Re: Concatenate

Wed, 18 Mar 2015 06:40:02 GMT

have atatched

Re: Concatenate

Wed, 18 Mar 2015 06:45:28 GMT

Hey Kartik,

Use attached QVW hope it might help.

Regards,

Nitesh

Re: Concatenate

anbu1984 — Wed, 18 Mar 2015 06:58:13 GMT

Try this

RC:

LOAD [Entry No_],

[Item No_],

Quantity,

[Entry Type],

[Document No_] As Doc

FROM

RC.xlsx

(ooxml, embedded labels, table is Sheet1);

RC1:

Load [Entry No_],[Item No_],Quantity,[Entry Type],[Document No_] As Doc;

LOAD [Entry No_],

[Item No_],

Quantity,

[Entry Type],

[Document No_]

FROM

RC.xlsx

(ooxml, embedded labels, table is Sheet2)

where not exists (Doc,[Document No_]);

You will get two rows with all fields as Nulls. This is because you have empty rows in Sheet1. You have to delete those rows.

Re: Concatenate

Wed, 18 Mar 2015 07:33:36 GMT

Hi,

Try this script.

Table1:

LOAD

[Entry No_],

[Item No_],

Quantity,

[Entry Type],

[Document No_]

FROM (Source Path);

Concatenate (Table1)

LOAD

[Entry No_],

[Item No_],

Quantity,

[Entry Type],

[Document No_]

FROM (Source Path)

Where [Document No_] <> RC-32538;

Re: Concatenate

hariprasadqv — Wed, 18 Mar 2015 07:46:10 GMT

Hi,

use where condetion along with not exists.