topic Re: Sum of quantity Where type = 'Order' in QlikView

Sum of quantity Where type = 'Order'

engishfaque — Wed, 04 Feb 2015 14:51:07 GMT

Dear All,

I'm trying to add products by using "Wildmatch" and "Where type='Order', but it's giving me some sort of abnormal output, such as attached sample files.

My requirement is that, I need sum of each product (wildmatch product) individually and display individually in straight table, such as given below:

Required Output

newProductSKU	newQuantity
NI-Gadsden35	1270
BB-13133	246

Kindly find attached sample files.

Please share me script, dimension and expressions. I'm using QlikView Personal Edition.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity Where type = 'Order'

engishfaque — Wed, 04 Feb 2015 16:09:07 GMT

Any update?

Re: Sum of quantity Where type = 'Order'

sunny_talwar — Wed, 04 Feb 2015 16:27:14 GMT

Is this what you are looking for? PFA

Best,

Re: Sum of quantity Where type = 'Order'

engishfaque — Wed, 04 Feb 2015 16:28:52 GMT

Dear Sunindia,

Please share me script. I'm using QlikView Personal Edition.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity Where type = 'Order'

sunny_talwar — Wed, 04 Feb 2015 16:29:49 GMT

Main:

LOAD prod_sku,

If(WildMatch(Upper(prod_sku), '*GADSDEN35*'), 'Type1', If(WildMatch(Upper(prod_sku), '*13133*'), 'Type2')) as Product_SKU_Type,

type,

quantity

FROM

MyFile.xlsx

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

Join(Main)

LOAD Product_SKU_Type,

Sum(quantity) as newQuantity

Resident Main

Where not IsNull(Product_SKU_Type)

Group By Product_SKU_Type;

Re: Sum of quantity Where type = 'Order'

giakoum — Wed, 04 Feb 2015 16:34:27 GMT

Please see attached

if you need to group further, the 3 sku that contain GADSDEN35 then you should create a new field with a if statement and use that in your chart :

if(WildMatch(Upper(prod_sku), '*GADSDEN35*'), 'NI-Gadsden35', sku) as newsku

or similar

Main:

LOAD prod_sku,

type,

quantity

FROM

MyFile.xlsx

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

temp:

LOAD prod_sku,

Sum(quantity) as newQuantity

Resident Main

Where

WildMatch(Upper(prod_sku), '*GADSDEN35*', '*13133*')

Group By

prod_sku;

DROP Table Main;

Re: Sum of quantity Where type = 'Order'

engishfaque — Wed, 04 Feb 2015 16:41:05 GMT

Dear Sunidia,

Sum of "13133" is not coming right. Currently sum is 256 whenever the required sum should be 246 as you can check in excel file just apply the filter in excel file and goto prod_sku column search>contain>type 13133, search and check in quantity column sum.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity Where type = 'Order'

engishfaque — Wed, 04 Feb 2015 16:43:03 GMT

Dear Loannis,

Please share me script. I'm using QlikView Personal Edition.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity Where type = 'Order'

giakoum — Wed, 04 Feb 2015 16:46:28 GMT

you are actually wrong, 256 is the correct value

Re: Sum of quantity Where type = 'Order'

sunny_talwar — Wed, 04 Feb 2015 16:52:00 GMT

Main:

LOAD RowNo() as UniqueKey,

prod_sku,

If(WildMatch(Upper(prod_sku), '*GADSDEN35*'), 'Type1', If(WildMatch(Upper(prod_sku), '*13133*'), 'Type2')) as Product_SKU_Type,

type,

quantity

FROM

MyFile.xlsx

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

Join(Main)

LOAD Product_SKU_Type,

Sum(quantity) as newQuantity

Resident Main

Where not IsNull(Product_SKU_Type) and type = 'Order'

Group By Product_SKU_Type;

Best,

Re: Sum of quantity Where type = 'Order'

sunny_talwar — Wed, 04 Feb 2015 16:53:47 GMT

I think there is another constraint in place which is type = 'Order'. If we put that one in place then the total will be 246.

Best,

Re: Sum of quantity Where type = 'Order'

engishfaque — Wed, 04 Feb 2015 17:24:48 GMT

Dear Loannis,

246 is right, 256 is wrong.

You can verify in excel file

Open excel file > select type = order > select prod_sku = 13133 > select quantity column and see the sum 246 in bottom of the file.

If need be, please let me.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity Where type = 'Order'

sunny_talwar — Wed, 04 Feb 2015 17:27:19 GMT

Did you try this new script?

Main:

LOAD RowNo() as UniqueKey,

prod_sku,

If(WildMatch(Upper(prod_sku), '*GADSDEN35*'), 'Type1', If(WildMatch(Upper(prod_sku), '*13133*'), 'Type2')) as Product_SKU_Type,

type,

quantity

FROM

MyFile.xlsx

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

Join(Main)

LOAD Product_SKU_Type,

Sum(quantity) as newQuantity

Resident Main

Where not IsNull(Product_SKU_Type) and type = 'Order'

Group By Product_SKU_Type;

Best,

Re: Sum of quantity Where type = 'Order'

engishfaque — Wed, 04 Feb 2015 18:24:21 GMT

Dear Sunidia,

Your solution is working fine but field "type" is coming in a dashboard. I have to display field "type" as well.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity Where type = 'Order'

sunny_talwar — Wed, 04 Feb 2015 18:27:23 GMT

Not really able to understand what you want? You want to display type?

Re: Sum of quantity Where type = 'Order'

engishfaque — Wed, 04 Feb 2015 18:33:11 GMT

Dear Sunidia,

Yes, I want to display type as listbox.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity Where type = 'Order'

giakoum — Wed, 04 Feb 2015 18:33:52 GMT

I did. the only difference is type :

.....

Where

WildMatch(Upper(prod_sku), '*GADSDEN35*', '*13133*')

and type = 'Order'

add it and you will be fine

Re: Sum of quantity Where type = 'Order'

sunny_talwar — Wed, 04 Feb 2015 18:39:06 GMT

Then why can't you display the list box for type?

Re: Sum of quantity Where type = 'Order'

engishfaque — Wed, 04 Feb 2015 18:55:51 GMT

Dear Sunidia,

Field "type" is not going to be displayed.

Now, I made a resident table through which I displayed the field type.

Actually, I found solution by using your (user: Sunidia) script and (user: Loannis) script. Therefore, I'm facing issue.

Now all the problems are solved.

Kind regards,

Ishfaque Ahmed

Re: Sum of quantity Where type = 'Order'

sunny_talwar — Wed, 04 Feb 2015 18:58:27 GMT

Great, I am glad your issue has finally resolved.

Best,