topic Sum of quantity based on relations in QlikView

Sum of quantity based on relations

engishfaque — Thu, 05 Feb 2015 00:27:30 GMT

Dear All,

I'm going to add the quantity of ProductSKU and ChildSKU fields.

For example (real scenario):

relations.xlsx file consist on all the parent and child relations.

In relations.xlsx file, threre is a ChildSKU = "NI-MF-006" with having 7 ParentsSKU, such as illustrated below:

relations.xlsx
parentSKU	ChildSKU
NI-70701	NI-MF-006
NI-70701-ZZ	NI-MF-006
NI-70701-ZZ1	NI-MF-006
NI-70701-ZZ2	NI-MF-006
NI-MF-006-ZZ1	NI-MF-006
NI-R6501-Gadsden35-MF-006-Set3	NI-MF-006
NI-R6501-MF-006-Set2	NI-MF-006
NI-MF-00623	NI-MF-006

salesYTD2014_testing.xlsx file consist on quantity of each individual items (parent and child) such as illustrated below:

prod_sku	sum(quantity)
NI-MF-006	540
NI-70701	13
NI-70701-ZZ	20
NI-70701-ZZ1	58
NI-70701-ZZ2	10
NI-MF-006-ZZ1	141
NI-R6501-Gadsden35-MF-006-Set3	227
NI-R6501-MF-006-Set2	247
NI-MF-00623	82

1. relations.xlsx file consist on relations

2. salesYTD2014_testing.xlsx file consist on quantity

Required Output:Sum of quantity must be sum of all the ParentSKU and ChildSKU in against of NI-MF-006 such as given below.

prod_sku	sumofquantity
NI-MF-006	1338

Kindly find attached files.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity based on relations

engishfaque — Thu, 05 Feb 2015 00:32:21 GMT

Dear All,

Please share script, dimension and expression; I'm using QlikView Personal Edition.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity based on relations

engishfaque — Thu, 05 Feb 2015 07:42:59 GMT

Dear Experts,

I really need your help, please help me.

If you need any clarification, just tell me.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity based on relations

jyothish8807 — Thu, 05 Feb 2015 07:53:43 GMT

Hi Ishfaque,

How did you get out put as:

prod_sku	sumofquantity
NI-MF-006	1338

There are 8 child entries so output should be 8*540 = 4320

Please correct me if I am wrong.

Regards

Re: Sum of quantity based on relations

Thu, 05 Feb 2015 08:13:03 GMT

Not sure if this is what you are but maybe:

Relations:

LOAD ParentSKU,

ChildSKU,

Ratio

FROM

relationship.xlsx

(ooxml, embedded labels, table is composite);

Concatenate

Load Distinct

ChildSKU,

ChildSKU as ParentSKU,

Ratio

Resident Relations;

LOAD type,

prod_sku as ParentSKU,

quantity

FROM

salesYTD2014_testing.xlsx

(ooxml, embedded labels, table is [YTD-2014]);

If you select NI-MF-006 as the ChildSKU it gives the desired result

Re: Sum of quantity based on relations

engishfaque — Thu, 05 Feb 2015 08:15:34 GMT

Dear Jyothish,

I'm going to correct you here, actually it is not about multiplication, It's all about addition.

So, please go to "relations.xlsx" file and apply filter in a file,

Search "NI-MF-006", you will find that "NI-MF-006" ParentSKU names exactly given below,

After that, you need to go in "salesYTD2014_testing.xlsx" file,

Where you need to apply filters and then go to column "type" and select "Order" and then go to column "prod_sku" and search individually each name, let's suppose you search "NI-MF-006" it's sum of quantity is "540", same as for "NI-70701", it's sum of quantity is "13" and same as for others to repeat the steps. Here is sum of quantity for each items,

Required output is field "prod_sku" consist on "NI-MF-006" and field "sum of quantity" consist on "1338". It is the sum of all the items including parents and child.

If you need any more clarification please let me know.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity based on relations

engishfaque — Thu, 05 Feb 2015 08:27:47 GMT

Dear Tyler,

It is not working such as i required, but 3re items quantity is coming right and remaining are missing. Please refer to attached image for more clarification.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity based on relations

jyothish8807 — Thu, 05 Feb 2015 08:31:45 GMT

Hi Ishfaque,

I guess this is what you are looking for:

script:

LOAD
type,
prod_sku as ParentSKU,
quantity
FROM
[Copy of salesYTD2014_testing.xlsx]
(ooxml, embedded labels, table is [YTD-2014]);

Relations:
LOAD ParentSKU,
ChildSKU,
Ratio
FROM
[Copy of relationship.xlsx]
(ooxml, embedded labels, table is composite);

Concatenate

Load Distinct

ChildSKU,
ChildSKU as ParentSKU,
Ratio

Resident Relations;

In front end create a straight chart:

in expression:

Sum ({<type={'Order'}>}quantity)

Regards

Re: Sum of quantity based on relations

Thu, 05 Feb 2015 08:34:03 GMT

Select NI-MF-006 as child SKU, don't select anything on ParentSKU, it will give you the right result

Re: Sum of quantity based on relations

jagan — Thu, 05 Feb 2015 08:43:19 GMT

Hi,

Try like this

Relation:

LOAD ParentSKU,

ChildSKU,

Ratio,

FROM

relationship.xlsx

(ooxml, embedded labels, table is composite);

Transaction:

LOAD type,

prod_sku as ChildSKU,

quantity

FROM

salesYTD2014_testing.xlsx

(ooxml, embedded labels, table is [YTD-2014]);

Variable: vParentSKU - =Chr(39) & Concat(ParentSKU, Chr(39) & ',' & Chr(39)) & Chr(39)

In Chart:

Dimension: ChildSKU

Expression: =Sum({<ChildSKU+={$(=vValidChildSKU)}>} quantity)

Regards,

Jagan.

Re: Sum of quantity based on relations

engishfaque — Thu, 05 Feb 2015 08:50:59 GMT

Dear Jyothish,

Many thanks, problem has been resolved with your and @tylor cool solution.

Kind regards,

Ishfaque Ahmed