Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Any update?
Is this what you are looking for? PFA
Best,
S
Dear Sunindia,
Please share me script. I'm using QlikView Personal Edition.
Kind regards,
Ishfaque Ahmed
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;
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;
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
Dear Loannis,
Please share me script. I'm using QlikView Personal Edition.
Kind regards,
Ishfaque Ahmed
you are actually wrong, 256 is the correct value
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