topic Re: One To Many Problem in QlikView

One To Many Problem

rebelfox — Wed, 25 Nov 2020 16:16:04 GMT

I have a need for a report to show invoice lines with values and show all the goods received numbers for each line.

I have a database table with Invoice lines with a financial value.

Each invoice line has a unique key of invoice number and position.

Each invoice line can have more than one Goods Received Number against it in a list

on another table with no unique key but they are linked by invoice number and position.

One invoice position could have 5 Goods Received numbers.

So if I load my invoice lines and my Goods Received table and link on on invoice number and invoice position I will get a problem.

The invoice line with a financial value will be displayed many times.

Imagine an invoice line for $100 with five Goods Received Numbers displayed on a sheet in a table object.

On my table object I will get 5 rows each for $100, each with a different Goods Received Number.

What I need is 1 line with $500 on the invoice line table and another table that lists the 5 Goods Received Numbers.

Can this be done? I suspect I am missing a concept somewhere.

Re: One To Many Problem

sinanozdemir — Mon, 28 Mar 2016 13:48:07 GMT

Do you happen to have a sample data?

Thanks

Re: One To Many Problem

maxgro — Mon, 28 Mar 2016 14:04:03 GMT

maybe you can split the $100 amount of the invoice table

in 5 * $ 20 amount in the records of the Goods Received table

this could be made in the script

Re: One To Many Problem

maxgro — Mon, 28 Mar 2016 14:06:58 GMT

this is another option at the chart level (in the attachment)

hope to inderstand your question

Re: One To Many Problem

rebelfox — Mon, 28 Mar 2016 14:38:31 GMT

This is a hasty example.

I added a single table object on my sheet that is made up of all columns and I got duplicate lines as expected.

For example I00001-001 has five rows so that looks like a value of 500.

If however I create a table object just for the invoices positions columns and another table object just for the

goods received field, I actually get what I want. I don't get why this is the case. I would expect the underlying

data to be the same in both cases. Do table objects de-duplicate displayed rows by default?

Is there a way to upload a QVD on a posting? I would have liked to upload my example.

Invoice_Positions:
LOAD * INLINE [
Invoice, Invoice_Position, Invoice_Position_Value, CompositeKey
I00001,001, 100, I00001-001
I00001,002, 200, I00001-002
I00001,003, 100, I00001-003
I00002,001, 10, I00002-001
I00003,001, 50, I00003-001
I00004,001, 100, I00004-001
I00005,001, 10, I00005-001
I00005,002, 80, I00005-002
];

Goods_Received:
LOAD * INLINE [
CompositeKey,Goods_Received_Number
I00001-001, G01
I00001-001, G02,
I00001-001, G03
I00001-001, G04,
I00001-001, G05,
I00001-002, G06,
I00002-001, G07,
I00005-002, G08
];

Re: One To Many Problem

rebelfox — Mon, 28 Mar 2016 14:39:12 GMT

Re: One To Many Problem

sinanozdemir — Mon, 28 Mar 2016 14:42:16 GMT

If you can, try to upload the qvd. You should be able to upload the qvd.

Re: One To Many Problem

rebelfox — Mon, 28 Mar 2016 14:42:57 GMT

OK found the upload option. I need to use advanced editor. See sample attached.

Re: One To Many Problem

sinanozdemir — Mon, 28 Mar 2016 14:49:44 GMT

See the below screenshot:

If you go to the presentation tab and check "Omit Rows Where Field is NULL" for the CompositeKey field, you will get the same results as in Table Object 2 and 3.

Hope this helps.

Re: One To Many Problem

sinanozdemir — Mon, 28 Mar 2016 14:53:14 GMT

No, scratch this. I don't think this does the trick.

Re: One To Many Problem

rebelfox — Mon, 28 Mar 2016 14:57:52 GMT

Why does displaying the invoice position and Goods received data in one table object behave differently to displaying the invoice positions in one table object and the goods received table in another table object?

Do Qlikview tables objects de-duplicate on rows?

Re: One To Many Problem

rebelfox — Mon, 28 Mar 2016 15:24:34 GMT

OK tables and Straight Tables do exclude duplicate rows on presentation.

I found this briefly described at http://qlikviewmaven.blogspot.se/2011/07/duplicate-rows-in-qlikview-table.html

So that explains the different behaviour when splitting the results into two table objects on the sheet.

However if I do any total expressions anywhere on the sheet against my invoice value, it would be to high

counting all occurrences of the invoice position value so that is not quite right either.

So I am kind of back where I started with a problem of a 1:M representation.

Re: One To Many Problem

rebelfox — Mon, 28 Mar 2016 15:25:42 GMT

Sounds painful and complex.

Re: One To Many Problem

rebelfox — Mon, 28 Mar 2016 15:33:15 GMT

Didn't see this before I responded to your prior post. I see you have helpfully described your theory as a solution.

Examining now.