Store Conditional value

[locus]

Hi All,

Working with a commercial invoice, there are unique reference numbers and weighted delivery time averages on each Invoice.

To illustrate:

INVOICE1 | REF_NR1 | 5.000
INVOICE1 | REF_NR2 | 6.000
INVOICE1 | REF_NR3 | 7.000
INVOICE1 | REF_NR5 | 8.000

I need to return only the REF_NR of the row that has the highest weighted average value.
So in this case REF_NR5.

There is a calculated column : MAX(weighted average for INVOICE_NR), so the value comes back in each row (This may or may not be useful!).
How do I create a column that brings back only the REF_NR for the row with the highest weighted average.

Again to illustrate the required result:

INVOICE1 | REF_NR1 | 5.000 | 8.000 | REF_NR5
INVOICE1 | REF_NR2 | 6.000 | 8.000 | REF_NR5
INVOICE1 | REF_NR3 | 7.000 | 8.000 | REF_NR5
INVOICE1 | REF_NR5 | 8.000 | 8.000 | REF_NR5

It seems like a weird thing to need to do but the set is just part of a greater requirement.

Any tips??

Cheers,
Locus

[anjan]

Hi,

Select invoc_colm,max(ref_value) from table1 group by invoc_colm

for above sql u would get

INVOICE1 |8.000


Now join the above data set to your main dataset based on the invoice col and value to get
"REF_ value"

Result will be like below

INVOICE1 | REF_NR5 | 8.000


now join this again to join with main data set based on invoice and red values and you will get the
result as


The above can be done in cognos as well.


Regards,
Anjan

[MFGF]

Hi All,

Working with a commercial invoice, there are unique reference numbers and weighted delivery time averages on each Invoice.

To illustrate:

INVOICE1 | REF_NR1 | 5.000
INVOICE1 | REF_NR2 | 6.000
INVOICE1 | REF_NR3 | 7.000
INVOICE1 | REF_NR5 | 8.000

I need to return only the REF_NR of the row that has the highest weighted average value.
So in this case REF_NR5.

There is a calculated column : MAX(weighted average for INVOICE_NR), so the value comes back in each row (This may or may not be useful!).
How do I create a column that brings back only the REF_NR for the row with the highest weighted average.

Again to illustrate the required result:

INVOICE1 | REF_NR1 | 5.000 | 8.000 | REF_NR5
INVOICE1 | REF_NR2 | 6.000 | 8.000 | REF_NR5
INVOICE1 | REF_NR3 | 7.000 | 8.000 | REF_NR5
INVOICE1 | REF_NR5 | 8.000 | 8.000 | REF_NR5

It seems like a weird thing to need to do but the set is just part of a greater requirement.

Any tips??

Cheers,
Locus

Hi,

Is this a dimensional or a relational package? I'm assuming relational?

One approach would be to create a second query containing the Invoice Number, the Reference Number and the delivery time average. The query would need a filter with the expression [weighted average] = maximum([weighted average] for [Invoice_Nr])

You would then join this to your main query using a JOIN object in Query Explorer (hint - drag in a new query, drop a join object onto this, then drag your existing two queries into the inputs to the join). Specify the join based on Invoice Number 1..1 <--> 1..1 and then in the query the join feeds into, bring in the required items from the underlying two queries. Finally, point your list to this new query.

This assumes that within an invoice there will only be a single reference number with the highest average. What would happen if an invoice had two or more references sharing the highest average?

MF.

[locus]

Thanks both.

MF,

After I compile the new joined query, selecting the List property and changing the source query to NEW QUERY,
on execution I get several errors, despite all required columns being imported

eg: QE-DEF-0359 The query contains a reference to at least one object '[qty_inv]' that does not exist.

What other steps are required to successfully 'point' the list at the joined query?
It does let me drop additional items from the new query into the list so it is seeing the list as NEW QUERY, but it doesn't execute.

locus

[MFGF]

Thanks both.

MF,

After I compile the new joined query, selecting the List property and changing the source query to NEW QUERY,
on execution I get several errors, despite all required columns being imported

eg: QE-DEF-0359 The query contains a reference to at least one object '[qty_inv]' that does not exist.

What other steps are required to successfully 'point' the list at the joined query?
It does let me drop additional items from the new query into the list so it is seeing the list as NEW QUERY, but it doesn't execute.

locus

Hi,

Can you make sure that there are no items in your list that are still hanging around from the old query? It sounds to me like this might be the case. What happens if you temporarily cut (don't delete) the List from your page and bring in a new list based on the query? Does this work? You can always undo the steps to put the original list back afterwards...

Cheers!

MF.

[locus]

Hi MF,

After much fiddling, it seems Informix needs the filter to be after auto-aggregation.
Otherwise some random syntax error is throw up.

The weighted average doesn't exist, it is created with a series of query expressions, so somewhere in all that it was unhappy.
Results look ok though.

In the joined query, the 1:1 join on invoice was splitting a quantity field by the total number of order lines.
I have divided this field by the count of order lines for now until I find a proper way to sort that out.

Thanks (Both) for the feedback.

locus