merge tables in a QV script

[#1]

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

[#2]

hi felix,
so try:

TableC:
load columnB
resident tableB;

left join(TableC)
load columnA
resident tableA;

[#3]

Hi Fabio,

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

1. The number of rows can not be more than table B contains.
2. All the data is in Table B.
3. 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

[#4]

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

must be:

1. The number of rows can not be more than table A contains.
2. All the data is in Table A.
3. 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...

[johnw]

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;