join or concatenate two tables that are the result of union of 2 crossed tables

[Anonymous]

Hello,

I appreciate your help on this....

I need to join or concatenate two tables that originally belongs from two cross tables each. I tried unsuccessful union and concatenation statements.  The attached script result in empty.

Thanks in advance for your help.

CrossTable(Month,Units,4)
UnitsIN:
LOAD Status,
Period,
"Product_name",
"Stock_Number",
"Ene_UNI" as "Ene",
"Feb_UNI" as "Feb";
SQL SELECT Status,Period,"Product_name","Stock_Number","Ene_UNI","Feb_UNI"
FROM "BSALE".dbo."Sellin";

CrossTable(Month,Sales,4)
SalesIN:
LOAD Status,
Period,
"Product_name",
"Stock_Number",
"Ene_CR" as "Ene",
"Feb_CR" as "Feb";
SELECT Status,Period,"Product_name","Stock_Number","Ene_CR","Feb_CR"
FROM "BSALE".dbo."Sellin";

NoConcatenate
ResultIN:
Load *
Resident UnitsIN;

inner Join (ResultIN)
Load *
Resident SalesIN;

Drop Tables UnitsIN,SalesIN;

CrossTable(Month,Units,4)
UnitsOut:
LOAD Status,
Period,
"Product_name",
"Stock_Number",
"Ene_UNI" as "Ene",
"Feb_UNI" as "Feb";
SQL SELECT Status,Period,"Product_name","Stock_Number","Ene_UNI","Feb_UNI"
FROM "BSALE".dbo."Sellout";

CrossTable(Month,Sales,4)
SalesOut:
LOAD Status,
Period,
"Product_name",
"Stock_Number",
"Ene_CR" as "Ene",
"Feb_CR" as "Feb";
SELECT Status,Period,"Product_name","Stock_Number","Ene_CR","Feb_CR"
FROM "BSALE".dbo."Sellout";

NoConcatenate
ResultOut:
Load *
Resident UnitsOut;

inner Join (ResultOut)
Load *
Resident SalesOut;

Drop Tables UnitsOut,SalesOut;

//Here the issue

TableINOUT:

Load *

RESIDENT ResultIn;

Join

Load *

Resident ResultOut;

Drop Table ResultIn;

Drop Table ResultOut;

[marcus_sommer]

I think a look in the table-viewer and the document-log would be useful to see which loadings with how many records are executed and which tables does exists. A possible issue here could be the inner join if the key-values didn't match in any way - turning into a left/right join would at least keep some data maybe at the first step for checking.

- Marcus

[Anonymous]

Hello Marcus, thanks for your reply.  

I just realize that in my original script i'm limiting the number of output records using 'First" function at Load and at SQL statement too.   Apparently this was the cause why the final table has "empty" result.  

After eliminating both sentences and starting to work with the whole data pool the script run as expected.

Thanks in advance for your advice.

Alex