Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist III
Specialist III

Sum of Quantity based on match

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

                               

ProductQuantity
NI-R6501-Gadsden35-MF-006-Set31
ADF-JPS-0011
NI-gadsden353
ADF-JPS-001-ZZ01
NI-R6501-Gadsden351
ADF-JPs-001-ZZ11
NI-R6501-Gadsden35-Set22


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

Required Output

ProductQuantity
Gadsden357
ADF-JPS-0013

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

Tags (1)
15 Replies
Highlighted
Specialist
Specialist

Re: Sum of Quantity based on match

Hi!

I had a similar problem in one project of mine...

If you know the list of these names you want to show, you can match them in the script.

Load

Product,

if(Capitalize(Product) like '*GADSDEN35*', 'Gadsden35',

  if(Capitalize(Product) like '*ADF-JPS-001*' ,'ADF-JPS-001', ........)) as ProductNew.

In my case, there were few values to be matched... if you have lots of them, maybe it can be difficult

Elena

Highlighted
Specialist III
Specialist III

Re: Sum of Quantity based on match

Dear Elena,

I have large set of data.

My data file consist on 387,615 records. It's difficult.

Kind regards,

Ishfaque Ahmed

Highlighted

Re: Sum of Quantity based on match

Please specify how your Product names can be identified in your product field?

Regards

Marco

Specialist III
Specialist III

Re: Sum of Quantity based on match

Dear Marco,

My product names are different from starting to end but if there is any one word match to each other then it must be add.

Such as given above example input and output.

Kind regards,

Ishfaque Ahmed

Highlighted

Re: Sum of Quantity based on match

This is probably impossible task as having let say

"ABC"

and "ABC1"

the matching values will be:

A

B

C

AB

ABC...

Highlighted
Specialist III
Specialist III

Re: Sum of Quantity based on match

Dear Robert,

Yes It's. Any suggestion or alternative?

Kind regards,

Ishfaque Ahmed

Highlighted

Re: Sum of Quantity based on match

If you can't define an algorithm, there will be no solution ...

regards

Marco

Highlighted

Re: Sum of Quantity based on match

Maybe I'm wrong but if you are selling/buying those products you must have defined some unique identification for them.

Otherwise this business is kind of "unique" one...;)

Can you dig deeper in your database?

Maybe you will find something that would help here.

Highlighted
Specialist III
Specialist III

Re: Sum of Quantity based on match

Dear Robert/Marco,

Yes you are right.

I'm trying to find pattern, as I can solve it.

Kind regards,

Ishfaque Ahmed