Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with 2 columns I want to do join on itself, and result table should contain multiple records for each combination of Table1.Column1 and Table1.Column2.
I am currently creating the table with default join, something like
Data:
Load Year, Month
from Table1;
join
Load Year as Year1,
Month as Month1
from Table1;
Table2:
Load
Year,
Month,
Year1,
Month1
Resident Data;
but since the record count of my table is 1M plus (I have many columns assigned for each 'Year' column), this is very timing consuming and script is failing due to huge data size. Below is my current table(Table1) and I want the final result table (Table2) to look like.
I guess dynamic for loop will take time and script will fail bcoz of huge data size. Any alternative ?
Table1
Year | Month |
---|---|
1996 | 71995 |
1997 | 81995 |
1998 | 91995 |
Final Result:
Table2
Year | Month |
---|---|
1996 | 71995 |
1996 | 81995 |
1996 | 91995 |
1997 | 71995 |
1997 | 81995 |
1997 | 91995 |
1998 | 71995 |
1998 | 81995 |
1998 | 91995 |
Thank you in advance.
You can split into two tables from single table like below
Approach 1
LOAD * Inline [
Year
1996
1997
1998
];
LOAD * Inline [
Month
71995
81995
91995
];
Approach 2:
Table1:
LOAD * Inline [
Year, Month
1996, 71995
1997, 81995
1998, 91995
];
NoConcatenate
LOAD Year as Year Resident Table1;
LOAD Month as Month Resident Table1;
DROP Table Table1;
Try this ways because Join is good here.
SourceData:
LOAD * Inline
[
Year, Month
1996, 71995
1997, 81995
1998, 91995
];
NoConcatenate
LOAD Year Resident SourceData;
Join
LOAD Month Resident SourceData;
DROP Table SourceData;
Regards,
Anand