topic Re: Creating Buckets (on size and selling price) in QlikView

Creating Buckets (on size and selling price)

israrkhan — Tue, 11 Aug 2015 01:05:57 GMT

Hi Guys,

i have to create bucket for product sold, and product size,

i have a product table, product table is linked to Model Table, and Model table linked to Transaction table. in the transaction table i have my quantity and selling price.

i have defined the buckets range in the below excel file. the product column in the below excel is linked to my Product column in product table. i dont have product column in the transaction table, so how to do it.

kindly help me, how to achieve this, in the load script or front end.. idea please..

i would appreciate your help.

Bucket Table:

Prodcut:	Indicator	Minimum	Maximum	Label
TV	SIZE	0	26	26" ↓
TV	SIZE	26	32	32"
TV	SIZE	32	38	37"
TV	SIZE	38	43	40" - 42"
TV	SIZE	43	52	46" - 50"
TV	SIZE	52	58	55"
TV	SIZE	58	999	60"↑
Mobile	Price	0	75	ULC (<$75)
Mobile	Price	75	150	Low ($75-$150)
Mobile	Price	150	300	Mid ($150-$300)
Mobile	Price	300	500	High($300-$500)
Mobile	Price	500	600	Super High($500-$600)
Mobile	Price	600	9999	Ultra High($600 and above)
Tablets	Price	0	100	ULC (<$100)
Tablets	Price	100	200	Low ($100-$200)
Tablets	Price	200	300	Mid ($200-$300)
Tablets	Price	300	400	High($300-$400)
Tablets	Price	400	500	Super High($400-$500)
Tablets	Price	500	9999	Ultra High($500 and above)

Re: Creating Buckets (on size and selling price)

maximiliano_vel — Tue, 11 Aug 2015 15:19:03 GMT

You can use Interval Match between your Bucket Table and Transaccion Table

JOIN ([Bucket Table]) IntervalMatch (Qty) LOAD Minimum, Maximum RESIDENT [Bucket Table];

The Qty is your Producto Quantity in your transaction table

Re: Creating Buckets (on size and selling price)

maxgro — Tue, 11 Aug 2015 15:23:10 GMT

I think (just an idea) you have to add the Product (using Product --> Model --> Transaction if possible) to the fact table

and then use interval match (or 2 interval match, one for Price / selling Price, the other for Size / Quantiy) to join the Product in fact table with the excel Product

Re: Creating Buckets (on size and selling price)

israrkhan — Tue, 11 Aug 2015 15:33:49 GMT

Dear Massimo,

i really tried, but could not get any solutions yet.

because my Bucket Table is linked to Product Table, and product table is linked to Model table, model table linked to Transaction table.

i have Quantity and Price in Transaction table, and I have size in Model table.

i was thinking to to ask you or Gysbert to help me..

i can explain you well, or can share a sample data, if you willing to help me...

i will really appreciate it ..

Many Thanks

Re: Creating Buckets (on size and selling price)

israrkhan — Tue, 11 Aug 2015 15:53:57 GMT

i tried this one

left join(Sellout) IntervalMatch(SellOut.PRICE)

LOAD

Minimum,

Maximum

from abc.

but it giving me wrong data...see in the image

Re: Creating Buckets (on size and selling price)

qlikviewwizard — Wed, 12 Aug 2015 06:12:11 GMT

Hi IsrarKhan

Can you share your sample QVW with the data to demonstrate your issue? Thank you.

Re: Creating Buckets (on size and selling price)

israrkhan — Wed, 12 Aug 2015 06:48:33 GMT

Thank you..

if i attache AN EXCEL file, than moderator will take one day to approve...

any how, i am attaching an excel file with data, in the next reply, may be i got lucky...

Re: Creating Buckets (on size and selling price)

israrkhan — Wed, 12 Aug 2015 06:51:30 GMT

see the attached file, with sample data, all Tables,

my table are linked as below..

Bucket --> Product--> Model-->Transaction.

i have Quantity and Price In Transaction table. and Bucket range of price in Bucket table.

i have size in Model Table, and Bucket range of size in Bucket table....

kindly let me know, if any thing is not clear...

Many Thanks

Re: Creating Buckets (on size and selling price)

israrkhan — Wed, 12 Aug 2015 06:59:04 GMT

expected result... as shown in attached image in pie chart...

Re: Creating Buckets (on size and selling price)

jonathandienst — Wed, 12 Aug 2015 07:11:36 GMT

This topic is a duplicate of Re: Bucket (based on selling price and size of product)

Please do not repeat or repost duplicates of a question.

Re: Creating Buckets (on size and selling price)

israrkhan — Wed, 12 Aug 2015 07:22:07 GMT

yes it is...

because yesterday when i created, it was being moderated, and moderator took 14 hours to approve the post. so at that time i tried to create another,

now i tried to delete the topic, but it says "unauthorized" even it is created by me..

so the qlik community in not working as it must me...

Re: Creating Buckets (on size and selling price)

israrkhan — Wed, 12 Aug 2015 07:26:12 GMT

Dear Jonathan,

if you could help me on the topic,

will appreciate it..

i will try to remove the duplicate post..sure..

but dont know, some time why they hold the post for the moderator, even with simple text...

Many Thanks

Re: Creating Buckets (on size and selling price)

jonathandienst — Wed, 12 Aug 2015 07:27:54 GMT

No problem

Re: Creating Buckets (on size and selling price)

kavita25 — Wed, 12 Aug 2015 07:39:53 GMT

what is the calculation of %??

Re: Creating Buckets (on size and selling price)

israrkhan — Thu, 13 Aug 2015 06:04:00 GMT

if size is dimension, size is in Model Table..size bucket is in Bucket table..

% is sum(Price)/ sum(total price)..

Re: Creating Buckets (on size and selling price)

jonathandienst — Thu, 13 Aug 2015 06:17:48 GMT

I see one data problem after looking at your Excel file. You have two bucket ranges for price - 1 for Mobile and another for Tablets. But there is no indication any of the other tabs of the Prodcut B (Product sp?) values, so I don't know which bucket range to use.

Re: Creating Buckets (on size and selling price)

israrkhan — Thu, 13 Aug 2015 07:52:23 GMT

Dear Jonathan.

thank you..

please you can add new column into excel, as new indicator.

i have access to database, i will create same column in my database here...

Re: Creating Buckets (on size and selling price)

jonathandienst — Thu, 13 Aug 2015 08:14:46 GMT

Post an updated Excel file when you are done.

Re: Creating Buckets (on size and selling price)

Digvijay_Singh — Thu, 13 Aug 2015 08:54:02 GMT

Hi,

I am not seeing Product against some models used in transaction, tried to verify in excel as well. Can you revert why it is so?

Please see attached QVW, I was just trying to understand data better. Also Product Code and Product having different format, which one you wanted to use for analysis.

Re: Creating Buckets (on size and selling price)

israrkhan — Sun, 16 Aug 2015 13:58:49 GMT

if you look in to first sheet(Bucket) you can see Product B column, which will be linked to Product Column in Product sheet(second sheet).

please ignore the product column (first column) in Bucket Sheet..

Many Thanks