Sum of Quantity based on match

[engishfaque]

Dear All,

I have a scenario where I have to merge products and calculate the quantity.

For example: Product name is "Gadsden35", this text can be occurred in first, middle or in last of product name as well as name can be in upper or lower case such as illustrated in listed below table.

Input

Product	Quantity
NI-R6501-Gadsden35-MF-006-Set3	1
ADF-JPS-001	1
NI-gadsden35	3
ADF-JPS-001-ZZ0	1
NI-R6501-Gadsden35	1
ADF-JPs-001-ZZ1	1
NI-R6501-Gadsden35-Set2	2

Now, I have to merge them and calculate the Quantity such as Gadsden35 quantity = 7

Required Output

Product	Quantity
Gadsden35	7
ADF-JPS-001	3

Please note, product names vary in my data source file. So, Gadsden35 is not always remain the same as the selection vary the product names vary.

Kind regards,

Ishfaque Ahmed

[vikasmahajan]

Hi

Ishfaque

As  Robert says there should be unique code primary key in master using this analysis can done but arriving results from such scenario  is not a best practice .

Vikas

[qlikoqlik]

Hi Ish

Why cant you use an if statement and Wildmatch(Procut,'Gadsden35' to have a field with a flag Gadsden35  or notGadsden35

then sum the quantity for the flag

Thanks

Padma

[robert_mika]

The Product Name is not (yet) know in advance.

It can be anything within this data set.

[engishfaque]

Dear Robert,

Exactly, product name is not known. The product name should be most common words between all the records.

Kind regards,

Ishfaque Ahmed

[Anonymous]

Dear Ishfaq,

In one of our applications, we use a look up file (like and excel) where we store these list of predefined Product names. In the beginning it is considerable work to list down all these product names (ofcourse with  help from business).

But once done, we store is separately and we also provide an input field in the report where the user can key new products directly into the report (as and when new products are created) and the report updates the  excel by the click of  a macro enabled button (or if there are limited users you can provide access to the file directly).

Normally the report results are refreshed with new additions after the next refresh. You may also use direct discovery if the user intends to see the updated report results immediately.

[prieper]

I would tackle this with a couple of loops, like

(aircode)

//     List of strings representing Products

Products2Search:

LOAD * INLINE [Prod

GADSDEN

JPS

....];

//     Base Table, should have suffix "0"

Input_0:

LOAD Product, Quantity, NULL() AS Productgroup  FROM .....;

//     Loops through all possible Product-Strings

FOR i = 1 TO NOOFROWS('Products') - 1

LET sProduct = PEEK('Product', i, 'Products2Search')

LET sTableName = 'Input_' & i;

LET sCurrentTable = 'Input_' & (i-1);

$(sTableName):

LOAD

Product,

Quantity,

IF(WILDMATCH(Product, '*' & $(sProduct) & '*'), '$(sProduct)', ProductGroup)               AS ProductGroup

RESIDENT

$(sCurrentTable);

DROP TABLE $(sCurrentTable);

NEXT i

The above table "Products2Search" may well be an external table.

HTH

Peter