Concatenate and left join in a loop

[martyn_birzys]

I've read numerous posts and tried various ways of getting this work, but I either get blank tables (NULLs I presume), or three duplicate tables with massive synthetic keys. How do I solve this?

PO: //table placeholder, as I'm concatenating later
LOAD * Inline 
[temp1, temp2
1, 1 ];


for each CoName in 'Company1', 'Company2', 'Company3'


t_PO:  //temporary table
LOAD POPOrderReturnID,
  '$(CoName)' as [Company Name],
    DocumentNo as [PO Number],
    DocumentStatusID,
    DocumentPrintStatusID, //+10 more fields
    TotalGrossValue;
SQL SELECT * FROM "$(CoName)".dbo.POPOrder;


Concatenate
t_PO: //concatenate archived data
LOAD POPOrderReturnID,
  '$(CoName)' as [Company Name],
    DocumentNo as [PO Number],
    DocumentStatusID,
    DocumentPrintStatusID, 
    TotalGrossValue;
SQL SELECT * FROM "$(CoName)".dbo.POPOrderArchive;


left join
t_PO:  //replaces ID with Name
LOAD DocumentStatusID,
    Name as "PO Status";
SQL SELECT DocumentStatusID,
    Name
FROM "$(CoName)".dbo.DocumentStatus;


drop field DocumentStatusID;


left join
t_PO: //replaces another ID with Name
LOAD DocumentPrintStatusID,
    Name as "PO Print Status";
SQL SELECT DocumentPrintStatusID,
    Name
FROM "$(CoName)".dbo.DocumentPrintStatus; //+2 more joins like this


drop field DocumentPrintStatusID;


Concatenate  //the final table
PO:
LOAD *
Resident t_PO;


DROP Table t_PO;


NEXT


DROP Fields temp1, temp2;

[sgrice]

To show your appreciation can you Set Action HELPFULL or Press Like on anything that you like or fine helpful

This really helps make long Discussion easier to read.

[martyn_birzys]

You are right, I should mark my comments as helpful!