Why does this not work: I load a qvdfile into a table HstDat_GLEntry, this table I join with my measure HstDat_SalesInvoice.
After the Join I load data into a table Map_IncludingRevenueAccounts from an excel file. After this load I want to clean up my masterfile:
I make a new table (SalesInvoiceLedger) from the existing table (HstDat_SalesInvoice) when AX_GLAccountNumber exists in HstDat_SalesInvoice as GLAccountID
The statement is Resident HstDat_SalesInvoice
where EXISTS (AX_GLAccountNumber, GLAccountID);
When I reload my script I have no records left and my new table SalesInvoiceLedger is empty.
When I change the statement from Where Exists in Where not exists all the numbers in my Excelfile are not loaded in my new table
Here below is my script:
/* Historical Data Loading */
HstDat_GLEntry:
noconcatenate LOAD
Company & DataLevel & VoucherNumber as GLAccountIDKey,
Company,
DataLevel,
LoadingDate,
GLAccountID,
PostingDate,
// VoucherNumber,
VoucherNumber as InvoiceNumber,
SubLedgerVoucher
FROM [$(DATADIR)qvd\HstDat_GLEntry.qvd] (qvd)
;
HstDat_SalesInvoice: LOAD
Company,
DataLevel,
LoadingDate,
InvoiceNumber,
PostingDate,
Year(PostingDate) as Year,
Month(PostingDate) as Month,
InvoiceLineNumber,
LineAmount,
CustomerID,
FROM [[$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd)
;
// a Left Join with HstDat_SalesInvoice
Left Join (HstDat_SalesInvoice) Load
*
Resident HstDat_GLEntry; Drop Table HstDat_GLEntry;
Map_IncludingRevenueAccounts:
noconcatenate Load
AX_GLAccountNumber,
Active
FROM [$(DATADIR)excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is including_RevenueAccounts)
where Active = 1;
SalesInvoiceLedger:
noconcatenate LOAD
*
Resident HstDat_SalesInvoice
where EXISTS (AX_GLAccountNumber, GLAccountID); DROP Table HstDat_SalesInvoice;
exit script;
Hope someone can help me or give me a hint what I do wrong
regards
