Compare Two Field From 2 Tabels

[matancha]

Hello All.

I have this situation:

     2 stright-table. (Row 2 is expression)

PartArc.PartName	Standard value(Kg)	Std.value-Prod(Kg)
	Sum(PartArc.Coef * Serial.Quant)	,(Sum(PartArc.Coef * Serial.Quant)/Serial.Quant) *  Sum(Aline.Quant)
	431.82	446.21
300010032	84.15	86.96
300010033	252.54	260.96
300020008	2.52	2.60
300020012	1.71	1.77
300030007	5.04	5.21
300040012	47.16	48.73
300050003	38.70	39.99

Transorder.PartName	Actual value(Kg)	Gap(%)
	Sum(Transorder.Quant)	(Prod-Act)/Act
	657.8	-
300010032	155.24	-
300010033	428.07	-
300020008	4.04	-
300020012	2.7	-
300030007	7.91	-
300050003	59.84	-

What I Want to Show Is:

PartName	Standard value(Kg)	Std.value-Prod(Kg)	Actual value(Kg)	Gap(%)
	431.82	446.21	657.8	-
300010032	84.15	86.96	155.24	85.955
300010033	252.54	260.96	428.07	259.958
300020008	2.52	2.60	4.04	1.604
300020012	1.71	1.77	2.7	0.767
300030007	5.04	5.21	7.91	4.208
300040012	47.16	48.73	-
300050003	38.70	39.99	59.84	38.99

How can I do that ( PartName Dimension?) ?

Table View:

Thks.

[matancha]

Hi Stefan,

I have take a quick look, and it's look better....

I hope that's the way.

I'll check it on my document on sunday (a short vacation...).

Thanks a million!!!

Matan.

[swuehl]

Hi Matan,

you're welcome.

What I basically have done is that I added the creation of overall PartName List in the load script and very simple expressions in the straigt table. So after I got the right idea, I think it is quite straigt forward.

If you need more info, please let me know.

Best regards and have a nice short vacation,

Stefan

[matancha]

Hi Stefan.

It's Work!!!

Tnks!!!

Matan.

[matancha]

Hi Stefan.

Just one question...

Why do you need this:

" where not exists (PartName, PartArc.PartName) "

on  the script?

It's work fine without this.

Tnks you in advance -

Matan.

[swuehl]

Hi Matan,

you're results are the same without that filter?

That's strange, if I remove that part, I get double value in in the comparison table, column 3 (recipe ingredients), for all ingredients that are used in recipe as well as actually used e.g. apple in Applecake = 1720 instead of 860.

I thought, that because of the double entries in the Partname table, the sum is executed twice. With given expression, I limited Partname table to one entry per ingredient.

(I already added the number of entries in the table in the Partname Listbox as expression).

If this expression has no effect in your application, I am a bit clueless.

Could you check that you get the same results in both cases in the comparision table, column recipe ingredients?

But I am glad, that you got it working.

Have a nice week,

Stefan

[matancha]

Hi Stefan.

Sorry for the late response...

It's my mistake. In my model, on the script I use the Load Distinct statment instead of "where not exsist..."  , So I'm not get duplicate records.

What your opinion?  If it's good? Wich method is better?

Thank you -

Matan.

[swuehl]

Hi Matan,

I think the two methods are equivalent in our case.

{As far as I know, the load distinct method will restrict the table to distinct values even on further adding fields / rows (further loads to the logical table, even without Distinct keyword), but even that should be ok.}

I am glad, that we got it working, it was a good exercise for me, too.

Regards,

Stefan