topic Re: Compare Two Field From 2 Tabels in QlikView

Compare Two Field From 2 Tabels

matancha — Wed, 29 Jun 2011 06:40:55 GMT

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

swuehl — Wed, 29 Jun 2011 14:19:03 GMT

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

matancha — Wed, 29 Jun 2011 20:01:07 GMT

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

swuehl — Wed, 29 Jun 2011 22:19:18 GMT

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

matancha — Thu, 30 Jun 2011 08:37:37 GMT

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

swuehl — Thu, 30 Jun 2011 11:03:57 GMT

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

swuehl — Thu, 30 Jun 2011 12:04:39 GMT

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

matancha — Thu, 30 Jun 2011 13:28:46 GMT

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

swuehl — Thu, 30 Jun 2011 13:43:19 GMT

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

swuehl — Thu, 30 Jun 2011 14:08:26 GMT

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

matancha — Thu, 30 Jun 2011 17:58:38 GMT

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

swuehl — Fri, 01 Jul 2011 08:21:19 GMT

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

Re: Compare Two Field From 2 Tabels

matancha — Sun, 03 Jul 2011 09:30:18 GMT

Hi Stefan.

It's Work!!!

Tnks!!!

Matan.

Re: Compare Two Field From 2 Tabels

matancha — Sun, 03 Jul 2011 12:48:14 GMT

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.

Re: Compare Two Field From 2 Tabels

swuehl — Mon, 04 Jul 2011 09:21:26 GMT

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

matancha — Wed, 06 Jul 2011 07:08:33 GMT

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

swuehl — Wed, 06 Jul 2011 12:43:50 GMT

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