Create a table made of two (for each)

[ziabobaz]

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!

[sunny_talwar · Accepted solution]

Why don't you just do a Join between the two tables

MainTable:

LOAD Date

RESIDENT [Calendar];

Join (MainTable)

LOAD Company,

    Account

RESIDENT [CompanyTable];

[Anonymous]

try

CARTHESIAN_TABLE:

LOAD * resident MainTable;

outer join

LOAD * resident CompanyTable:

drop table MainTable,CompanyTable;

[ziabobaz]

That's correct
I complicated everything. Should have used join. 

Thank you!

[ziabobaz]

Thank you, that's either working.