topic Re: variable Bucket Ranges Of Product in QlikView

variable Bucket Ranges Of Product

israrkhan — Sun, 30 Aug 2015 17:25:52 GMT

Dear All.

i have a problem in creating Bucket ranges for my product table. i asked the same question few days back, but no help could help me.

just trying again, might be some one could share his\her thoughts or any hint,...

will appreciate very much...

Requirement...

i have bucket range defined in excel file, so its easy for user to changes the range , when he want..

you can see my data model in attached sample application...you will understand...

in the chart, i want to show bucket range on Model Size in a pie chart,

i want to see how much quantity sold on each bucket range...

i want to use Label from Bucket Table as Dimension, and Sum(sellout.qty) as expression...

Sellout.QTY is in the Sellout table..

kindly ask me, i will explain in detail....

Many Thanks...

Re: variable Bucket Ranges Of Product

Anonymous — Sun, 30 Aug 2015 18:14:34 GMT

Hi,

I had a similar problem...

I would join Model table with Bucket table and then use in the script the expression:

IF(Model size>=Minimum AND Model size<Maximum, Label, Null()) AS new_label

You can use this new label for the chart.

Re: variable Bucket Ranges Of Product

israrkhan — Sun, 30 Aug 2015 18:19:17 GMT

Thanks Elene.

but i have bucket ranges defined on Product level, not Model level...

have you seen my data model..my tables are linked as below.

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

we have defined bucket ranges on product, and each product has many models, and Model linked to transaction table,

because transaction are on model code based...

thanks

Re: variable Bucket Ranges Of Product

israrkhan — Sun, 30 Aug 2015 18:19:43 GMT

and if you can share a sample, would be helpful.

Re: variable Bucket Ranges Of Product

Anonymous — Mon, 31 Aug 2015 07:13:12 GMT

Yes, I saw your data model.

You should create an unique table with Bucket, Product and Model by joining them.

Then you can use the expression above and eliminate the null new_label.

You can see also this post:

https://community.qlik.com/message/843255#843255

Re: variable Bucket Ranges Of Product

Digvijay_Singh — Mon, 31 Aug 2015 08:19:39 GMT

Did you try using intervalmatch to link your model size with minimum and maximum of bucket table. But for that I think model size needs to be in transaction table.

Also I am having apprehensions if your Bucket table and Product table associated with Product_B can properly identify the range uniquely as it can associate many records either way.

As Elena suggested, you need to join tables further to get desired information.

Re: variable Bucket Ranges Of Product

israrkhan — Mon, 31 Aug 2015 10:06:40 GMT

Thanks Digvijay

for you reply...

i have not tried interval match, can you please share an example of interval match in my case..

secondly As Elena suggested, she said i need to join my Bucket table with Model table..

problem is i dont have bucket range on model level but on product level.

my tables are linked like below:

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

let say i have Product called TV in product table, and TV has many models in model table, and each TV size is in Model table. and i have model code in transaction table as well.

so in the bucket table i have Product TV and range like minimum, maximum and label...

if you see in the data model in the attached sample, you will understand...

Many Thanks...

Re: variable Bucket Ranges Of Product

israrkhan — Mon, 31 Aug 2015 13:58:35 GMT

Thanks Elena...

Unique Table ?

do you meant , that i should join Model table into product, and then joint bucket table in to product, so finally i will have only one table "Product" linked to Transaction table my data model?

i will have only two tables in my data model "Products" and "Transaction" ?

Thanks Again...

Re: variable Bucket Ranges Of Product

Anonymous — Mon, 31 Aug 2015 14:09:39 GMT

Yes, this is my idea.

Two tables: Product and Sellout.

Then, in Product, you have to delete record where new_label is null.

This is the way I used... Unfortunatly, I don't know if there is a more clever solution...

In a final step, if you want, I think you can split again Prodcut into the 3 original tables, but in my case, I didn't do this step.

Re: variable Bucket Ranges Of Product

israrkhan — Mon, 31 Aug 2015 14:21:17 GMT

Thanks Elena..

it seems a nice idea...

i think i should follow your idea, only for this Bucket report, for other report i can keep my data model as it is..

because i have many reports and others tables like Brand , Location, channel, Selling Type and so on, so i dont need to change my whole data model, because few product does not have size, like Mobile, Accessories etc...Only TV and tablets and few other product have size..

but only for Bucket report, i can create an extra table by joining Product, Model and Bucket tables..

is this will be ok?

Re: variable Bucket Ranges Of Product

Anonymous — Mon, 31 Aug 2015 14:31:39 GMT

Yes, I think so... you just have to try!

I hope you can reach your aim.

If you found another type of solution, more clever, please share it.

Thank you.