16 Replies Latest reply: Jul 6, 2011 8:43 AM by Stefan Wühl

# Compare Two Field From 2 Tabels

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.

• ###### Compare Two Field From 2 Tabels

Hi plzmatanch,

You want a table with dimension part.partname as first column, right?

You can use set analysis to limit the expressions to the parcart resp. transorder.partname equal to part.partname:

e.g. Sum( {\$<PartArc.ParcName=Part.PartName> } PartArc.Coef * Serial.Quant)

other sum accordingly

Regards,

Stefan

• ###### Compare Two Field From 2 Tabels

Thank you - Stefan.

My problem is that the part.PartName (the common field) is the "father" of  Both: Transorder.PartName and PartArc.ParName.

I try another way to explain the problem.

Let say that we bake a cake. the cake is The Part.PartName (Ex: Part.PartName = 1000).
Now, all the Ingredients de facto are under Transorder.PartName (Ex. Transorder.PartName = 1003, 1005, 1008 ,1033), and compatible quantities at: Sum(Transorder.quant)
In contrast, all the recipe Ingredients are under PartArc.PartName(Ex. PartArc.PartName = 1003, 1008, 1012, 1022), and compatible quantities at: Sum(PartArc.Coef * Serial.Quant)

So my problem is to create straight-table (maybe somthing else?)  that include all the part name with compatible expression:

Part     Sum(Transorder.quant)     Sum(PartArc.Coef * Serial.Quant)     Gap

1003     X1                                          Y1                                                             X1-Y1

1005     X2

1008     X3                                          Y2                                                             X3-Y2

1012                                                    Y3

1022                                                    Y4

1033     X4

Hope that it's clearly....

Thanks all!

• ###### Compare Two Field From 2 Tabels

Hi plzmatanch,

I hope I do understand...

Well, I still would go with the "father" part.PartName as dimension and filter the expression with set expression as indicated above. I think this should result in the table you want to have.

In your example, what is "Part" if not part.PartName (it must be superset of both other partName sets, right)?

I assume also that PartNames are identical in all sets (father and sons), though then the data sets are  redundant and could be errornous.

If it is possible, you could post an example .qvw (with limited data set if needed), this might help to understand your problem.

Regards,

Stefan

• ###### Re: Compare Two Field From 2 Tabels

Hello all

and Stefan, Thnks again...

I attached simple example, that demonstrate my problem.

My problem is to combine the tow Straigth table.

Where the result (for "AppleCake") Is:

 PartName PartDes Sum(PartArc.Coef * Serial.Quant) SUM(Transorder.Quant) Gap (Recipe-Transorder) 1359.2 2070 710.8 1008 flour 166 200 34 1009 Sugar 249.2 670 420.8 1022 Apple 860 1000 140 1077 Caramel 84 50 -34 1033 Wine 150 150

Hope It's better.

Thaks a lot!

• ###### Re: Compare Two Field From 2 Tabels

Hi plzmatanch,

I think I understand now. Thanks for the example.

I tried something, but no full solution yet. I added a table with a list of all PartNames (see script in example).

I used this to generate a straight table dimension, all ingredients (recipe and actual used) are now listed correctly, also PartDes.

I still have a problem with limiting the expressions:

- In the attached example, the expressions always calculate the total over all parts in each line, not limiting to the actual part name.

I tried around with set expression but no success until now.

- If a add the two commented lines in the script (add Links to PartNames in transorder and recipe), I get correct results for the straight table you want, but the data model now has a synthetic key of course, which leads to problems in your recipe ingredients table.

Maybe this shows a way (maybe not) to go on, if I have time, I may work on this a bit more later on.

Best regards,

Stefan

• ###### Re: Compare Two Field From 2 Tabels

Hi again,

this looks a little bit better now.

Results seems to be ok now. Still a synthetic key.

Best regards,

Stefan

• ###### Re: Compare Two Field From 2 Tabels

Thank you very much, Stafan - for your help.

This implement (with synthetic key)  cause other objects in my original document - to be wrong - (not in the Cake example).

Maybe there is another way?

Thanks again..

Matan.

• ###### Re: Compare Two Field From 2 Tabels

Hi,

well, I also prefer my initial approach (see version2), but I can't get the limitation of e.g. PartArc.PartName to PartName correct. It returns an empty set.

If we could get this corrected, I think we are done.

Regards,

Stefan

• ###### Re: Compare Two Field From 2 Tabels

This won't let me rest ;-)

I think I have a work around for my limitations problem and no syn key anymore.

Getting close, I hope.

Does not look so bad too me.

Cheers,

Stefan

• ###### Re: Compare Two Field From 2 Tabels

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.

• ###### Compare Two Field From 2 Tabels

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.

Best regards and have a nice short vacation,

Stefan

• ###### Re: Compare Two Field From 2 Tabels

Hi Stefan.

It's Work!!!

Tnks!!!

Matan.

• ###### Re: Compare Two Field From 2 Tabels

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.

Matan.
• ###### Re: Compare Two Field From 2 Tabels

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

• ###### Re: Compare Two Field From 2 Tabels

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.

• ###### Compare Two Field From 2 Tabels

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