Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to eliminate duplicate lines

Hi

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

 

 

1 Solution

Accepted Solutions

Re: How to eliminate duplicate lines

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

5 Replies

Re: How to eliminate duplicate lines

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

Not applicable

Re: How to eliminate duplicate lines

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

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

Not applicable

Re: How to eliminate duplicate lines

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

Not applicable

Re: How to eliminate duplicate lines

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

Community Browser