Join Data Without Definite Unique Identifier

[#1]

Can you guys help me about this problem?

this is the scenario

TABLE 1					TABLE 2
DATE	NAME	ID	CODE		TRANS	DATE	CODE
2013	LOIS	2	1		55	2013	1
2013	REY	3	1		66	2013	1

how  can i achieve this output?

OUTPUT
DATE	CODE	NAME	ID	TRANS
2013	1	LOIS	2	55
2013	1	REY	3	66

[Gysbert_Wassenaar]

Well, you can do it by creating a field with the row number in both tables. But unless you can be sure the first row of table1 should go with the first row of table2, the second row of table1 with the second of table2 etc.... you can create bogus data.

Output:

load *, rowno() as row from ...table1source...;

join load *, rowno() as row from ...table2source...;

drop field row;

[flipside]

Hi,

What is the logic that says TRANS 55 goes with ID 2 and TRANS 66 goes with ID 3?

flipside

[#4]

my problem was It should have different TRANS..

[flipside]

Sorry I don't understand, can you explain further?

[#6]

Inputs

TABLE 1
DATE	NAME	ID	CODE
2013	LOIS	2	1
2013	REY	3	1

TABLE 2
TRANS	DATE	CODE
55	2013	1
66	2013	1

Ideally ouput

OUTPUT
DATE	CODE	NAME	ID	TRANS
2013	1	LOIS	2	55
2013	1	REY	3	66

or

OUTPUT
DATE	CODE	NAME	ID	TRANS
2013	1	LOIS	2	55
2013	1	REY	3	66

but my results are

OUTPUT
DATE	CODE	NAME	ID	TRANS
2013	1	LOIS	2	66
2013	1	REY	3	66
2013	1	LOIS	2	55
2013	1	REY	3	55

[flipside]

At the moment your join doesn't know which row to join to, so it takes TRANS 66 and joins it to both rows, then does the same with TRANS 55. This is why you get the output in your bottom table. However you are saying you want to assign TRANS 55 to a specific row and TRANS 66 to a different specific row, there must be some logic to this. Is it, as Gysbert says, to join the tables based on the row order, or do you just want the joins to be random, or is there some other information. Without clarification the problem cannot be answered.

[#8]

Unfortunately there's other column available that can be join..
Yes! How can I randomized the TRANS Column..

[Gysbert_Wassenaar]

If you just want a random TRANS value then you don't need table 2 at all. You can use the rand function.

TABLE1:

LOAD *, ceil(rand()*100) as TRANS INLINE [

    DATE, NAME, ID, CODE

    2013, LOIS, 2, 1

    2013, REY, 3, 1

];

Or just use rowno() or recno() if a sequential numbering is good enough.

[flipside]

This might be a solution as long as you are happy with a totally random selection of the available TRANS figures (which will mean you will have the same TRANS figure selected more than once and some not at all) ...

TABLE1:

load * inline [
DATE, NAME, ID, CODE
2013, LOIS, 2, 1
2013, REY, 3, 1];

INNER join
load DATE, CODE, concat(TRANS,',') AS TRANS, count(TRANS) AS count GROUP BY DATE, CODE;
Load * inline [
TRANS, DATE, CODE
55, 2013, 1
66, 2013, 1
77, 2013, 1
88, 2013, 1];

LEFT join
load DATE, NAME, ID, CODE, subfield(TRANS,',',CEIL(rand()*count)) as randTRANS resident TABLE1;

flipside