topic merge tables in a QV script in QlikView

merge tables in a QV script

Tue, 14 Sep 2010 17:23:30 GMT

Dear developers,

the following:

assume I have 2 resident tables in my script

table A with 1 column, in this column 2 kinds of data appear like this:

abcd

bcde

cdef

0001

defg

efgh

fghi

0002

etc.....

table B This one contains the numeric values of table A in 1 column as well

0001

0002

0003

0004

etc....

The end result should be a table C with 2 columns like the one hereunder:

0001 abcd

0001 bcde

0001 cdef

0002 defg

0002 efgh

0002 fghi

etc....

How can I have this result?

Thx in advance...

Felix

merge tables in a QV script

Tue, 14 Sep 2010 17:43:05 GMT

hi felix,
so try:

TableC:
load columnB
resident tableB;

left join(TableC)
load columnA
resident tableA;

merge tables in a QV script

Tue, 14 Sep 2010 20:42:13 GMT

Hi Fabio,

thx for your suggestion! 2 bad it didn't work out for me...I think I didn't explain the problem well.

The number of rows can not be more than table B contains.
All the data is in Table B.
The number value (0001, 0002 etc) in row N should be the key for the rows (were the characters are in!) before that one.

I thought Table A could be helpfully so I created that one in order to have all keys gathered in the load order.

Cheers

Felix

merge tables in a QV script

Tue, 14 Sep 2010 20:56:38 GMT

Sooo sorry in my last reply I mixed up TableA and B

must be:

The number of rows can not be more than table A contains.
All the data is in Table A.
The number value (0001, 0002 etc) in row N should be the key for the rows (were the characters are in!) before that one.

I thought Table B could be helpfully so I created that one in order to have all keys gathered in the load order.

hope this explains it better...

merge tables in a QV script

johnw — Wed, 15 Sep 2010 01:09:19 GMT

Here's one way:

A:
LOAD * INLINE [
Field1
abcd
bcde
cdef
0001
defg
efgh
fghi
0002
];

B:
LOAD * INLINE [
Field2
0001
0002
];

C:
NOCONCATENATE
LOAD
recno() as Sequence
,Field1
RESIDENT A
;
LEFT JOIN (C)
LOAD
Sequence
,Field1
,if(exists(Field2,Field1),Field1,peek(Field2)) as Field2
RESIDENT C
ORDER BY Sequence DESC
;
INNER JOIN (C)
LOAD Sequence
RESIDENT C
WHERE Field1<>Field2
;
DROP FIELD
Sequence
;

DROP TABLES
A
,B
;

But if the only reason you had table B was to make it easier to identify the numeric values and sequence in table A, then B is completely unnecessary, and it is simpler to go straight from A to C:

A:
LOAD * INLINE [
Field1
abcd
bcde
cdef
0001
defg
efgh
fghi
0002
];

C:
NOCONCATENATE
LOAD
recno() as Sequence
,Field1
RESIDENT A
;
LEFT JOIN (C)
LOAD
Sequence
,Field1
,if(num(Field1),Field1,peek(Field2)) as Field2
RESIDENT C
ORDER BY Sequence DESC
;
INNER JOIN (C)
LOAD Sequence
RESIDENT C
WHERE Field1<>Field2
;
DROP FIELD Sequence;
DROP TABLE A;