Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excluding Specific Data

Hey everyone,

my first week with QV was great and i found a lot of help here in the community so thank everyone for that

I fixed all the problems my report had except one that is not even a real problem but just an annoying thing ^^.

Description: The report is about Requisition's - Purchuase Orders - Invoices - Payments.

Requisitions Purchuase Orders and Invoices are perfect and i visualize all the needed data perfectly.

Instead in the payments part i have all the payments, even the one's not linked to any requisition or purchuase order (random example electricity invoice).

This falses my result when there is no selection made.

The aggregation system of QV at this point does something wird. Group all this invoices without project in a project " - " and shows the amount of the invoice, the invoice_id but not the invoice_num (even if is present in oracle) and marks it again with a " - ".

I cant make a SQL condition on the script like where project_id = NULL because in the table i use is not valorized and all the project_id are null so the query wont return anything.

Does anyone have an idea on how i can exclude this data from my report. A script condition will be even better so the data is not loaded at all.

Thanks everyone.

Regards,

Juan Pedro

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi JP,

"I have all the payments, even the one's not linked to any requisition".

Add a WHERE EXISTS clause to the end of your LOAD statement. Something like this:

RequisitionTable:

LOAD

   ReqNo,

   ReqNo AS TestReqNo,

   ReqDate,

   CustCode,

    ....

FROM .....etc;

PaymentsTable:

LOAD

   PaymentReqNo AS ReqNo,

   PayDate,

   ....

FROM .....

WHERE EXISTS (TestReqNo,PaymentReqNo);

DROP FIELD TestReqNo;

Note the use of a Test field as the first parameter, which is later dropped. You need to use the unchanged PaymentReqNo field name as the second parameter as that is what is available during the load.

Also note that with both tables having multiple common fields (date and key) you may want to CONCATENATE them into a TransactionTable.

Regards,

Jonathan

View solution in original post

3 Replies
swuehl
MVP
MVP

If payments is linked to Invoices, and you want to load only payments records with a valid link you can try something like

INVOICES:

LOAD * FROM INVOICES;

PAYMENTS:

LEFT KEEP (INVOICES) LOAD * FROM PAYMENTS;

Or use exists() function in a where clause to load only records with an existing value in Table Invoices from Table Payments.

Please also check out Henric's blog post:

http://community.qlik.com/docs/DOC-3412

Hope this helps,

Stefan

Anonymous
Not applicable
Author

Hi JP,

"I have all the payments, even the one's not linked to any requisition".

Add a WHERE EXISTS clause to the end of your LOAD statement. Something like this:

RequisitionTable:

LOAD

   ReqNo,

   ReqNo AS TestReqNo,

   ReqDate,

   CustCode,

    ....

FROM .....etc;

PaymentsTable:

LOAD

   PaymentReqNo AS ReqNo,

   PayDate,

   ....

FROM .....

WHERE EXISTS (TestReqNo,PaymentReqNo);

DROP FIELD TestReqNo;

Note the use of a Test field as the first parameter, which is later dropped. You need to use the unchanged PaymentReqNo field name as the second parameter as that is what is available during the load.

Also note that with both tables having multiple common fields (date and key) you may want to CONCATENATE them into a TransactionTable.

Regards,

Jonathan

Not applicable
Author

Hey thnks everyone for the tips .

I used the exists clause but i had to add a table to my selection to make it work. I didnt used the the extra field you suggested but i guess my english isnt that good to explain the problem.

cheers

Juan Pedro