Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Champion
Champion

Re: Sum of Quantity based on match

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

Highlighted
Creator II
Creator II

Re: Sum of Quantity based on match

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

Highlighted

Re: Sum of Quantity based on match

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

It can be anything within this data set.

Highlighted
Specialist III
Specialist III

Re: Sum of Quantity based on match

Dear Robert,

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

Kind regards,

Ishfaque Ahmed

Highlighted
Creator
Creator

Re: Sum of Quantity based on match

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.

Highlighted
Master II
Master II

Re: Sum of Quantity based on match

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