Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have built a QVDthat provides us the information on what has been ordered on apurchase order, received, vouchered and invoiced. Certain purchase ordershave more than one item on the order. We are trying to developa QlikView that shows the history of that item for all of these stagestogether.
Not all of the orderquantities for each item will arrive at the same time.
PO | Item | Receipt | Voucher | Voucher Qty | Invoice # |
35055 | A | 82506 | 394731 | 3600 | 50004631 |
35055 | A | 82506 | 394770 | 6323 | 50004551 |
35055 | A | 82506 | 394797 | 1200 | 50004552 |
35055 | A | 82536 | 394797 | 3600 | 50004552 |
35055 | A | 82776 | 397759 | 11400 | 50005298 |
35055 | A | 82776 | 397761 | 4800 | 50005295 |
35055 | A | 82776 | 397763 | 812 | 50005296 |
Receipts
Item | Receipt | Total |
A | 82506 | 33369 |
A | 82536 | 3600 |
A | 82776 | 51036 |
When I go to sum the receipts for item A, I am getting an answer that is equal to thesum total times the number of items that were received on that receipt. So in the above examples I have 33,369 instead of 11,123. This has 3items on the receipt. The third line has 51036, but it should be. This occurs for any item that has multiple items on a receipt. Sofor my data on an item, we have vouchered it284,785 of this item, but it is showing me that we havereceived 481,392.
Are we missing something in the design of the QVD or isthere something I need to do instructions regarding the summing that I need todo.
Thank you.
Here is the qvw with the information. PO 35055 has item 78742-20549-C that was receipt 82506. If you look at the Receipt Chart, receipt 82506 has a sum total of 33369 which is three times tht amount that was received on that reciept.
Thanks
When you create the QVD can you do a Load Distinct? Unless you have duplicates in data you will not get this issue.
If you can add couple of expressions as below and share the details?
Count([Voucher Qty] and Count(Distinct [Voucher Qty].
I would be happy to help you if you can share your QVD/QVW.
Regards,
Kiran.
Please provide a sample QVW to enable evaluation of your data model and expressions. If the data is sensitive, please use the scrambling funciton to make field unreadable.
Settings > Document Properties > Scrambling
I have posted qvw in my ealier posting.
Hi,
There are duplicate records. Quick solution is to use
sum(DISTINCT ReceivedQuantity)
instead of
sum(ReceivedQuantity)
Ideal Solution:
Split Voucher number, Invoice Number and Voucher Quantity into another table with a key on Reciept. Let me know if you need help in doing this.
Regards,
Kiran.