Sum of quantity Where type = 'Order'

[engishfaque]

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

[engishfaque]

Any update?

[sunny_talwar]

Is this what you are looking for? PFA

Best,

S

[engishfaque]

Dear Sunindia,

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

Kind regards,

Ishfaque Ahmed

[sunny_talwar]

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;

[giakoum · Accepted solution]

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;

[engishfaque]

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

[engishfaque]

Dear Loannis,

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

Kind regards,

Ishfaque Ahmed

[giakoum]

you are actually wrong, 256 is the correct value

[sunny_talwar]

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,

S