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: 
Anonymous
Not applicable

Sum from another field

Hey friends

i have a problem showing the the inventory qtyf rom another field which is linked (BOM).

Basically i have 3 tables

1. Inventory figures by Item

2. item relations (Multilevel BOM)

3. Item data ( item informations such  type and so on....)

Now i would like to show the stock of an subassambly by expression. Tried planty of things, but somehow i just dont get it done. how should the expression look like?

i have attached a sample .qvw which is reflecting my RL data

Would be great if somebody could help

Many thanks and rgs

Rob

24 Replies
varshavig12
Specialist
Specialist

Do you want something like this:

You can Hide the type.

Is this ok ?

Note: I guess my "SUB" is your "SKU NO"

Anonymous
Not applicable
Author

here we go, sample data for upload

InventoryQTY:

LOAD * Inline [

SKU NO    ,     QTY

1    ,     10

2    ,     12

3    ,     14

4    ,     16

5    ,     18

6    ,     20

7    ,     22

8    ,     24];

BOM:

load * Inline [

SKU NO    ,    SubAssembly

1    ,    3

1    ,    4

1    ,    5

1    ,    6

1    ,    7

1    ,    8

2    ,    3];

ItemInfo:

LOAD * Inline [

SKU NO    ,    Type

1    ,    Finish

2    ,    Finish

3    ,    Oil

4    ,    Oil

5    ,    Oil

6    ,    Fuel

7    ,    Fuel

8    ,    Fuel];

if all works the result should be

   

DimDimExpression(qty from Sub)
SKU NOSubAssemblyQTY
1314
1416
1518
1620
1722
1824
2314

or without SubAssembly Dim

  

DimExpression(qty from Sub)
SKU NOQTY
1114
214

or if the item type oil is specified, with and without SubAssembly as dimension

  

DimExpression(qty from Sub, type = oil)
SKU NOQTY
148
214

   

DimDimExpression(qty from Sub, type = oil)
SKU NOSubAssemblyQTY
1314
1416
1518
2314

many thansk for your support

varshavig12
Specialist
Specialist

Ok, I will check with this.

varshavig12
Specialist
Specialist

I guess you are considering SKU NO as SubAssembly.

Considering the first record from your output,

if SKU NO is 3 , SubAssembly is 3 then the value should be 14.

But, if SKU NO is 1,   SubAssembly is 3 then the value should be 10.

Anonymous
Not applicable
Author

Can you have a look to my sample to upload above? Bassically the step to link the qty is missing in your sample. What im trying is to get the also the QTY from the SKU NO in your sample

Thanks

Anonymous
Not applicable
Author

Jep i need both informations

SKU NO, QTY, SubAssembly, QTY

1,     10,           3,          14 this fieldvalue i need   

Just to display the figures from the InventoryQTY Table (SKU NO) and adding a dimension (SubAssembly) and displaying the item type 'Oil' is not my problem.

this could be easely solved by

sum({<Type = {'Oil'}>} QTY) expression

but im looking for an expression which will tell me the qty of the SubAssembly. So bassically i need to use the SubAssembly fieldvalue from the BOM: table and check in the InventoryQTY table, what the stock is.

varshavig12
Specialist
Specialist

I have to leave,

Check the script of the attached app.

Read the comments.

Anonymous
Not applicable
Author

ok i will check, hope it will work

many thanks for your support!!

Anonymous
Not applicable
Author

In your app you have renamed the field [SKU NO] into [SubAssembly] and the [SubAssembly] the other way around.

Doing this i will loose still one qty information. [SKU NO] quantity is mandatory, additionally i need the quantity from the subassembly.

Sample

SKU NO, QTY,      SubAssembly,      QTY

1,               10,           3,               14 this fieldvalue i need   

Just to display the figures from the InventoryQTY Table (SKU NO) and adding a dimension (SubAssembly) and displaying the item type 'Oil' is not my problem.

this could be easely solved by

sum({<Type = {'Oil'}>} QTY) as expression

but im looking for an expression which will tell me the qty of the SubAssembly. So bassically i need to use the SubAssembly fieldvalue from the BOM: table and check in the InventoryQTY table, what the stock QTY is.

something like

sum({<Item = {'sub from the relation table'} , Type = {'Oil'}>} QTY)

Thänks

varshavig12
Specialist
Specialist

Ahha! I understood,

We need something where we can assign SubAssembly number to SKU NO

Something like,

sum({<[SKU NO]={"$(=([SubAssembly]))"}>}QTY)

I'm not sure about its working.

I will try and let you know