Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am struggling with the following: i need to combine two tables into ResultTable as below.
MainTable:
Date |
---|
2018-01-01 |
2018-01-02 |
CompanyTable:
Company | Account |
---|---|
A | bank1 |
A | bank2 |
B | bank2 |
B | bank3 |
ResultTable:
Date | Company | Account |
---|---|---|
2018-01-01 | A | bank1 |
2018-01-01 | A | bank2 |
2018-01-01 | B | bank2 |
2018-01-01 | B | bank3 |
2018-01-02 | A | bank1 |
2018-01-02 | A | bank2 |
2018-01-02 | B | bank2 |
2018-01-02 | B | bank3 |
//I loaded all dates from my Calendar as a start:
MainTable:
LOAD
Date
RESIDENT [Calendar];
//Then for each Date I want to load Company-Account values from the CompanyTable
FOR EACH a in FieldValueList('Date')
Concatenate ([MainTable])
LOAD
Company,
Account
RESIDENT [CompanyTable];
NEXT a;
It does not work as I want - it just created the following table:
Date | Company | Account |
---|---|---|
A | bank1 | |
A | bank2 | |
B | bank2 | |
B | bank3 | |
2018-01-02 | ||
2018-01-02 | ||
2018-01-02 | ||
2018-01-02 |
I guess it is wrong to use Concatenate here, than what is the correct way?
Thank you!
Why don't you just do a Join between the two tables
MainTable:
LOAD Date
RESIDENT [Calendar];
Join (MainTable)
LOAD Company,
Account
RESIDENT [CompanyTable];
Why don't you just do a Join between the two tables
MainTable:
LOAD Date
RESIDENT [Calendar];
Join (MainTable)
LOAD Company,
Account
RESIDENT [CompanyTable];
try
CARTHESIAN_TABLE:
LOAD * resident MainTable;
outer join
LOAD * resident CompanyTable:
drop table MainTable,CompanyTable;
That's correct
I complicated everything. Should have used join.
Thank you!
Thank you, that's either working.