Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
Hi,
What is the logic that says TRANS 55 goes with ID 2 and TRANS 66 goes with ID 3?
flipside
my problem was It should have different TRANS..
Sorry I don't understand, can you explain further?
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 |
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.
Unfortunately there's other column available that can be join..
Yes! How can I randomized the TRANS Column..
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.
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