Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
I hope someone knows how I can solve this,
Thanks in advance
regards
Court van de Lisdonk
Try with Join / Concatenate
What is the issue you are getting?
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
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 :
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);
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);
Sorry bro, this is really not gonna help you.
Try to share small qvw file , that tell about requirement.
-Nagarjun
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
Please use field signatures as you used in your application
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..