Concatenate Resident Load to create every combination possible

[#1]

See below script.

The top script is pulling from two tables with a distinct list of Dates and CSNs

The second script should createe a list of every possible combination.  However, the Temp table does not get created.  I tried using Distinct in load.  What am i missing

Temp:
Load TDate
Resident TblDate;
Concatenate
Load TCSN
Resident TblCSN;

TblCSNDate:
NoConcatenate
load TCSN & TDate as CSNDate
Resident TblTempDate;
Drop table Temp;

[its_anandrjs]

Hi,

I suggest you need to load table some thing like below

TblDate:

load * inline

[

TDate

01/01/2011

];

TblCSN:

load * inline

[

TCSN

01/02/2011

];

Temp:

Load TDate as FieldA

Resident TblDate;

Concatenate

Load TCSN as FieldB

Resident TblCSN;

TblCSNDate:

NoConcatenate

load FieldA & FieldB as CSNDate

Resident Temp;

Drop table Temp;

Let me know if not works

HTH

Regards

Anand

[#3]

You want a matrix representing all possible combinations of TDate and TCSN, right?      Instead of the Concatenate Load TCSN do a    Join Load TCSN

[jonathandienst]

Hi

I am not sure what you are asking here. If the Temp table does not get created, then the rest of the script obviously won't work, but the problem lies outside what you showed in your post.

I assume, however, you want to create every possible combination of TDate and TCSN (this is called a cross join or cartesian join), then this should work:

TblCSNDate:
Load Distinct TDate
Resident TblDate;
Outer Join Load  Distinct TCSN
Resident TblCSN;

Cross joins must be used with care as they can rapidly grow in size and are often due to a mistake in a join - leading to the out of memory problems often posted here. But if your data set is not that big and you need all the possible combinations, then a cross join is waht you need.

Hope that helps

Jonathan