topic Re: Exists or Not Exists in QlikView

Exists or Not Exists

pacoli2013 — Thu, 13 Oct 2016 15:27:44 GMT

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

Re: Exists or Not Exists

Anonymous — Thu, 13 Oct 2016 21:24:48 GMT

Can you try with following script, This might solve your problem.

Map_IncludingRevenueAccounts:

noconcatenate Load

AX_GLAccountNumber,

Active

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

(ooxml, embedded labels, table is including_RevenueAccounts)

where Active = 1;

/* 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)

where EXISTS (AX_GLAccountNumber, GLAccountID)

;

SalesInvoiceLedger:

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;

Re: Exists or Not Exists

pacoli2013 — Thu, 13 Oct 2016 22:16:17 GMT

Sorry that doesn't work

Re: Exists or Not Exists

marcus_sommer — Fri, 14 Oct 2016 05:22:43 GMT

The changed load-structure from Ramesh should work with a small adjustment:

Map_IncludingRevenueAccounts:

noconcatenate Load

AX_GLAccountNumber,

Active

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

(ooxml, embedded labels, table is including_RevenueAccounts)

where Active = 1;

/* 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)

where EXISTS (AX_GLAccountNumber, Company & DataLevel & VoucherNumber)

....

- Marcus

Re: Exists or Not Exists

jonathandienst — Fri, 14 Oct 2016 05:41:13 GMT

I see no syntax errors (although there could be some optimisation). The problem could be that you are left joining the table with GLAccountID using join key Company, DataLevel, PostingDate and LoadingDate. If there are no matches, then no GLAccountIDs will exist in HstDat_SalesInvoice (they will be null or missing), and a null/missing value never Exists(), so no data is loaded.

Re: Exists or Not Exists

pacoli2013 — Fri, 14 Oct 2016 11:10:07 GMT

You are corrert, it works, thank you

Re: Exists or Not Exists

pacoli2013 — Fri, 14 Oct 2016 11:10:51 GMT

You are right, I have checked the data,

Thank you