topic Re: How to eliminate duplicate lines in QlikView

How to eliminate duplicate lines

Thu, 29 Nov 2012 10:48:36 GMT

I am working on a QV Model to determine orders that are invoiced , partially invoiced and outstanding.

I have 4 tables namely:

Order Header
Order Detail
Invoice Header
Invoice Detail

When an order is invoiced , it generates an invoice number, otherwise invoice number its shown as "****NEW***.

However i have the following issue where the result I am achieving there are duplicates per line for orders that are invoiced. See attached excel file called "Outcome Results"

The duplicate lines are highlighted in yellow in Table called "Current Result"
The ideal result i want to achieve is in Table called "Ideal Result"

How do I apply the scripting so that I dont have duplicates.

I have attached the excel file where the table sits as well as the QV Model

Please can you help

Thank you

kind regards

Nayan

Re: How to eliminate duplicate lines

Peter_Cammaert — Thu, 29 Nov 2012 12:23:47 GMT

Your model has everything thrown together in a single table. That means that all lines with an OrderID occur twice:

once from the invoices data
once from the orders data

That is, they occur twice if there are corresponding invoices. For example ORD002 has no invoices, so no duplicates. Use Table Viewer->Preview to check your data.

Your solution is to use QlikView as ... QlikView: construct an associative model with a few tables and appropriate keys.

Best,

Peter

Re: How to eliminate duplicate lines

Thu, 29 Nov 2012 12:33:34 GMT

Hi Peter

Thank you for your rersponse.

I am new at Qlikview and at scripting, hence I do not how to create an associative model. I just know basics. In the model that i have attached previously, can you assist with the scripting.

Thank you

kind regards

Nayan

Re: How to eliminate duplicate lines

Peter_Cammaert — Thu, 29 Nov 2012 12:58:09 GMT

Hi Nayan,

try this:

Load and join orders and orderlines in a table and call this one Orders
Load and join invoices and invoicelines in a second table and call this one Invoices.
While loading this last table, give all fields a prefix to avoid linking them to the corresponding fields in Orders.
Except for OrderID. That field should be used as a key tying the two together.

Teaching you how to write Load scripts would take me too far. But hey, QlikView has excellent Designer/Developer trainings that teach you how to master QlikView in a few days. Make sure to look into them if you want to do some serious development in QlikView.

Peter

Re: How to eliminate duplicate lines

Thu, 29 Nov 2012 13:45:40 GMT

Hi Peter

Will have a go and let you know. I think i know how to do the last part, I have not had experience with the first part of joining 2 tables and renaming it. But i will have a go.

regards

Nayan

Re: How to eliminate duplicate lines

Fri, 30 Nov 2012 11:38:55 GMT

Hi Peter

Just to let you know, I have come right with the scripting. It was the hard way but manage to do it .

Thank you

kind regards

Nayan