topic Re: Sum from another field in QlikView

Sum from another field

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sum from another field

sunny_talwar — Mon, 12 Dec 2016 14:35:49 GMT

What are you expecting to see?

Re: Sum from another field

Anonymous — Mon, 12 Dec 2016 14:50:00 GMT

lets say if i select item 1, i would like to see the qty of all oils

sku	type	qty	sub qty (oil)
1	finished	10	39

thansk

Re: Sum from another field

sunny_talwar — Mon, 12 Dec 2016 14:56:24 GMT

May be this:

Sum(TOTAL {<[SKU NO], TYPE = {'Oil'}>}QTY) * Avg(1)

Re: Sum from another field

Anonymous — Mon, 12 Dec 2016 15:11:15 GMT

it should just show the figures from

3	,	Oil
4	,	Oil
5	,	Oil

I should be able to add the dim sub if required

result should be

Item	Type	Sub qty (type oil)
1	finished	12
1	finished	13
1	finished	14

thanks

Re: Sum from another field

sunny_talwar — Mon, 12 Dec 2016 15:15:53 GMT

I am not sure I understand the logic behind this? Would you be able to explain what you are trying to do?

Re: Sum from another field

Anonymous — Tue, 13 Dec 2016 08:54:18 GMT

sure!

Lets say i have an SKU NO 1, which is build of components (SUB) 3 and 4. Now i would like to display by expresion the Sub stock quantities from the SKU NO. The link which Sub belongs to which item is done in the relation table.

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

Result should be

Dim	Dim	Expression (qty from sub oil)
SKU NO	SUB	QTY
1	3	12
1	4	13
1	5	14

the expression should also work if Dim SUB is removed

Dim	Expression (qty from sub oil)
SKU NO	QTY
1	39

Thanks Sunny

Re: Sum from another field

varshavig12 — Tue, 13 Dec 2016 09:52:07 GMT

Your exp should work.

Re: Sum from another field

Anonymous — Tue, 13 Dec 2016 10:04:32 GMT

Hi, can you eventually add the expression to the sample .qvw attached? Would be great, as i couldnt get it work ...

Thanks Varsha

Re: Sum from another field

varshavig12 — Tue, 13 Dec 2016 10:34:06 GMT

I'm using personal edition.

Re: Sum from another field

varshavig12 — Tue, 13 Dec 2016 11:09:34 GMT

Do you want something like this:

You can Hide the type.

Is this ok ?

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

Re: Sum from another field

Anonymous — Tue, 13 Dec 2016 11:12:21 GMT

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

Dim	Dim	Expression(qty from Sub)
SKU NO	SubAssembly	QTY
1	3	14
1	4	16
1	5	18
1	6	20
1	7	22
1	8	24
2	3	14

or without SubAssembly Dim

Dim	Expression(qty from Sub)
SKU NO	QTY
1	114
2	14

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

Dim	Expression(qty from Sub, type = oil)
SKU NO	QTY
1	48
2	14

Dim	Dim	Expression(qty from Sub, type = oil)
SKU NO	SubAssembly	QTY
1	3	14
1	4	16
1	5	18
2	3	14

many thansk for your support

Re: Sum from another field

varshavig12 — Tue, 13 Dec 2016 11:17:30 GMT

Ok, I will check with this.

Re: Sum from another field

varshavig12 — Tue, 13 Dec 2016 11:34:30 GMT

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.

Re: Sum from another field

Anonymous — Tue, 13 Dec 2016 11:38:29 GMT

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

Re: Sum from another field

Anonymous — Tue, 13 Dec 2016 11:59:50 GMT

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.

Re: Sum from another field

varshavig12 — Tue, 13 Dec 2016 12:00:45 GMT

I have to leave,

Check the script of the attached app.

Read the comments.

Re: Sum from another field

Anonymous — Tue, 13 Dec 2016 12:04:35 GMT

ok i will check, hope it will work

many thanks for your support!!

Re: Sum from another field

Anonymous — Tue, 13 Dec 2016 14:26:27 GMT

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

something like

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

Thänks

Re: Sum from another field

varshavig12 — Wed, 14 Dec 2016 04:15:19 GMT

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