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
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.
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.
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.
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.
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.
