topic Re: Concatenate two tables and just replacing a specified column in QlikView

Concatenate two tables and just replacing a specified column

Fri, 13 Sep 2013 04:03:32 GMT

Hello Experts,

I am starting to learn concatenate function in Qlikview and I want to replace column f2 from table 2 into table 1

so the intended result as the following table:

f1	f2	f3
a	a	c4
b	b	c5
c	b	c6
d	b	c2
e	c	c3

table2:

f1	f2	f3
a	a	c1
d	b	c2
e	c	c3

table1:

f1	f2	f3
a	b	c4
b	b	c5
c	b	c6

I used the code below and the result table turn out to be this:

f1	f2	f3
a	a	c1
a	b	c4
b	b	c5
c	b	c6
d	b	c2
e	c	c3

LOAD f1,

f2,

FROM

C:\table1.xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD f1,

f2,

FROM

C:\table2.xlsx

(ooxml, embedded labels, table is Sheet1);

PLEASE HELP!!! THANKS IN ADVANCE!!

Re: Concatenate two tables and just replacing a specified column

tresB — Fri, 13 Sep 2013 04:21:46 GMT

Try something like:

LOAD f1,

f2,

FROM

C:\table1.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD f1,

f2,

FROM

C:\table2.xlsx

(ooxml, embedded labels, table is Sheet1) where not exists(f1);

Re: Concatenate two tables and just replacing a specified column

Fri, 13 Sep 2013 04:36:25 GMT

try this code.

table2:

LOAD * Inline [

f1, f2, f3

a, a, c1

d, b, c2

e, c, c3

] ;

Join

table1:

LOAD * Inline [

f1, f2, f3

a, b, c4

b, b, c5

c, b, c6] Where not Exists(f1);

then output like this

f1	f2	f3
a	a	c1
b	b	c5
c	b	c6
d	b	c2
e	c	c3

see attached file.

Re: Concatenate two tables and just replacing a specified column

nizamsha — Fri, 13 Sep 2013 06:03:13 GMT

table2:

LOAD * Inline [

f1, f2, f3

a, a, c1

d ,b, c2

e ,c, c3

];

table1:

LOAD * Inline [

f1 ,f2, f3

a ,b ,c4

b ,b, c5

c ,b ,c6

] where not exists(f1);

Re: Concatenate two tables and just replacing a specified column

sujeetsingh — Fri, 13 Sep 2013 06:11:19 GMT

It seems some time the user posting the issue just forget to mention what is right and helpful.

Re: Concatenate two tables and just replacing a specified column

Fri, 13 Sep 2013 06:23:55 GMT

Hello Dear,

Using when not exists(f1) statement in the end I get this table:

f1	f2	f3
a	b	c4
b	b	c5
c	b	c6
d	b	c2
e	c	c3

which is not the one I am hoping to get...

I want to get this result:

f1	f2	f3
a	a	c4
b	b	c5
c	b	c6
d	b	c2
e	c	c3

Re: Concatenate two tables and just replacing a specified column

Fri, 13 Sep 2013 06:25:21 GMT

Hello Dear,

Using when not exists(f1) statement in the end I get this table:

f1	f2	f3
a	b	c4
b	b	c5
c	b	c6
d	b	c2
e	c	c3

which is not the one I am hoping to get...

I want to get this result:

f1	f2	f3
a	a	c4
b	b	c5
c	b	c6
d	b	c2
e	c	c3

Re: Concatenate two tables and just replacing a specified column

Fri, 13 Sep 2013 06:26:32 GMT

f1	f2	f3
a	a	c4
b	b	c5
c	b	c6
d	b	c2
e	c	c3

Thanks for the help, but this is the table I was trying to get...

PLEASE HELP!!

Re: Concatenate two tables and just replacing a specified column

nizamsha — Fri, 13 Sep 2013 06:28:20 GMT

LOAD * Inline [

f1, f2, f3

a, a, c1

d ,b, c2

e ,c, c3

] where not exists(f1);

table1:

LOAD * Inline [

f1 ,f2, f3

a ,b ,c4

b ,b, c5

c ,b ,c6

] ;

Re: Concatenate two tables and just replacing a specified column

tresB — Fri, 13 Sep 2013 06:29:03 GMT

Then Try:

LOAD f1,

f2,

FROM

C:\table2.xlsx

(ooxml, embedded labels, table is Sheet1) ;

LOAD f1,

f2,

FROM

C:\table1.xlsx

(ooxml, embedded labels, table is Sheet1) where not exists(f1);

Re: Concatenate two tables and just replacing a specified column

nizamsha — Fri, 13 Sep 2013 06:34:09 GMT

table1:

LOAD * Inline [

f1 ,f2, f3

a ,b ,c4

b ,b, c5

c ,b ,c6

] ;

table2:

LOAD * Inline [

f1, f2, f3

a, a, c1

d ,b, c2

e ,c, c3

]where not exists(f1) ;

paste this one u will get what u want

Re: Concatenate two tables and just replacing a specified column

tresB — Fri, 13 Sep 2013 06:40:01 GMT

PFA

Re: Concatenate two tables and just replacing a specified column

Fri, 13 Sep 2013 07:40:37 GMT

Hello Dear,

Sorry it does not produce this table

f1	f2	f3
a	a	c4
b	b	c5
c	b	c6
d	b	c2
e	c	c3

Re: Concatenate two tables and just replacing a specified column

Fri, 13 Sep 2013 07:43:23 GMT

Hello Dear,

seems like everyone is misunderstanding what I am asking for...my bad for state it clearly in the posting..

='(

I want to get this result:

f1	f2	f3
a	a	c4
b	b	c5
c	b	c6
d	b	c2
e	c	c3

Re: Concatenate two tables and just replacing a specified column

Fri, 13 Sep 2013 07:44:21 GMT

I am sorry that I could not open this using my free personal edition..