Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
matancha
Creator
Creator

Compare Two Field From 2 Tabels

Hello All.

I have this situation:

     2 stright-table. (Row 2 is expression)

PartArc.PartNameStandard value(Kg)Std.value-Prod(Kg)
Sum(PartArc.Coef * Serial.Quant),(Sum(PartArc.Coef * Serial.Quant)/Serial.Quant) *  Sum(Aline.Quant)
431.82446.21
30001003284.1586.96
300010033252.54260.96
3000200082.522.60
3000200121.711.77
3000300075.045.21
30004001247.1648.73
30005000338.7039.99

Transorder.PartNameActual value(Kg)Gap(%)
Sum(Transorder.Quant)(Prod-Act)/Act
657.8-
300010032155.24-
300010033428.07-
3000200084.04-
3000200122.7-
3000300077.91-
30005000359.84-

What I Want to Show Is:

PartNameStandard value(Kg)Std.value-Prod(Kg)Actual value(Kg)Gap(%)
431.82446.21657.8-
30001003284.1586.96155.2485.955
300010033252.54260.96428.07259.958
3000200082.522.604.041.604
3000200121.711.772.70.767
3000300075.045.217.914.208
30004001247.1648.73-
30005000338.7039.9959.8438.99

How can I do that ( PartName Dimension?) ?

Table View:

TableWiew.JPG

  

Thks.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

16 Replies
swuehl
MVP
MVP

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

matancha
Creator
Creator
Author

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!

swuehl
MVP
MVP

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

matancha
Creator
Creator
Author

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!

swuehl
MVP
MVP

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

swuehl
MVP
MVP

Hi again,

this looks a little bit better now.

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

Best regards,

Stefan

matancha
Creator
Creator
Author

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.

swuehl
MVP
MVP

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

swuehl
MVP
MVP

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