combine three tables

[pacoli2013]

Hello,

I have a Problem perhaps for you a very little one to solve. Let me explain: there are three tables:

Temp_Include:
NoConcatenate  LOAD
including_Company
FROM [$(DATADIR)\Excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is including_Companys)
WHERE (Active)=1;

Temp_Customers:
NoConcatenate LOAD
AX_SellToCustomerNumber
//     AX_Active
FROM [$(DATADIR)Excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is excluding_Customers)
WHERE (AX_Active)=1;

and

HstDat_SalesInvoice:
LOAD

Company &’’& DataLevel as CompanyKey

Company,
DataLevel,
LoadingDate,
CustomerID

FROM [$(DATADIR)qvd\Sales.qvd] (qvd);

I want to make one table out of three. There should be one table HstDat_SalesInvoice, where  company is equal including_company from table Temp_Include and CustomerID not equal AX_SellToCustomerNumber from Temp_Customers:

I think it should be something like this:

HstDat_SalesInvoice:

LOAD

Company &’’& DataLevel as CompanyKey
Company,
WHERE EXISTS (including_Company);
LOAD       
DataLevel,
LoadingDate,

CustomerID

FROM [$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd)
Where (CustomerID <> AX_SellToCustomerNumber);

I got the  next messages when I reload:

1. 1. Syntax error, missing/misplaced FROM: HstDat_SalesInvoice:
2. 2. Field not found - <AX_SellToCustomerNumber>

I hope someone knows how I can solve this,

Thanks in advance

regards

Court van de Lisdonk

[Anil_Babu_Samineni]

Try with Join / Concatenate

What is the issue you are getting?

[nagarjuna_kotha]

As per the ur above script , your problem will not solve,

How you want combine those three table without having any common fields .

please share proper information , that suite ur requiremnt.

-Nagarjun

[pacoli2013]

The problem i have is that data from sales ist 'dirty' so different data

should be excluded. For instance there are old company Numbers which should

be excluded, the temp-table has the right companynumbers. For customers:

the temp-table has Numbers from customers which don't exist and also be

excluded!!! Regards

Am Freitag, 30. September 2016 schrieb nagarjuna k :

[rupamjyotidas]

Maybe this(changes in bold). But a sample app might help

Temp_Include:

NoConcatenate  LOAD

including_Company

FROM [$(DATADIR)\Excel\Umsatzbildung001.xlsx]

(ooxml, embedded labels, table is including_Companys)

WHERE (Active)=1;

Temp_Customers:

NoConcatenate LOAD

AX_SellToCustomerNumber as CustomerID

//     AX_Active

FROM [$(DATADIR)Excel\Umsatzbildung001.xlsx]

(ooxml, embedded labels, table is excluding_Customers)

WHERE (AX_Active)=1;

HstDat_SalesInvoice:

LOAD

Company &’’& DataLevel as CompanyKey

Company,

WHERE EXISTS (including_Company);

LOAD      

DataLevel,

LoadingDate,

CustomerID

FROM [$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd)

Where not exists (CustomerID);

[rupamjyotidas]

More refined:

Temp_Include:

NoConcatenate  LOAD

including_Company as Company

FROM [$(DATADIR)\Excel\Umsatzbildung001.xlsx]

(ooxml, embedded labels, table is including_Companys)

WHERE (Active)=1;

Temp_Customers:

NoConcatenate LOAD

AX_SellToCustomerNumber as CustomerID

//     AX_Active

FROM [$(DATADIR)Excel\Umsatzbildung001.xlsx]

(ooxml, embedded labels, table is excluding_Customers)

WHERE (AX_Active)=1;

HstDat_SalesInvoice:

LOAD

Company &’’& DataLevel as CompanyKey

Company,

WHERE EXISTS (Company);

LOAD      

DataLevel,

LoadingDate,

CustomerID

FROM [$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd)

Where not exists (CustomerID);

[nagarjuna_kotha]

Sorry bro, this is really not gonna help you.

Try to share small qvw file , that tell about requirement.

-Nagarjun

[rajpreeths]

Hi..

You can try the following conditions to avoid those two errors..

1-》The first where exists should be replaced with following condition.

WHERE EXISTS(INCLUDING_COMPANY,COMPANY)

2-》The second where conditions can be changed as follows.

WHERE NOT EXISTS(AX_SellToCustomerNumber,Customer ID)

Please try this and let me know if this works as per your requirement

[rajpreeths]

Please use field signatures as you used in your application

[rajpreeths]

Use the above conditions with this load script as below..

HstDat_SalesInvoice:
LOAD

Company &’’& DataLevel as CompanyKey

Company,
DataLevel,
LoadingDate,
CustomerID

FROM [$(DATADIR)qvd\Sales.qvd] (qvd)

WHERE EXISTS(including_Company,Company)

AND not EXISTS(AX_SellToCustomerNumber,Customer ID);

Try this and let me know..