Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

Create Pareto Bins in Load Script

Hello,

Is there a way to create Pareto bins (say Top 80%,80-95% and Bottom 5%) in the load script?Say my data has Vendor Name and Sales. I'd like to create the above mentioned bins for this data. If you could you please explain with the help of an example. I desperately need help! TIA!

stalwar1vinieme12‌. Please help!

1 Solution

Accepted Solutions
sunny_talwar

May be this

Table:

LOAD * INLINE [

    Vendor,      Sales

    A,              1300

    B,              2200

    A,              1400

    B,              2100

    C,              2500

    A,              300

    C,              900

    D,              3200

    B,              2000

    D,              1500

    E,              1100

    F,              3500

    E,              1500

    G,              1400

    H,              600

    H,              900

    G,            1200

];

Left Join (Table)

LOAD Sum(Sales) as TotalSales

Resident Table;

TempTable:

LOAD Vendor,

  Sum(Sales)/TotalSales as SalesByVendor

Resident Table

Group By Vendor, TotalSales;

Left Join (Table)

LOAD Vendor,

  RangeSum(Peek('CumSalesByVendor'), SalesByVendor) as CumSalesByVendor,

  If(RangeSum(Peek('CumSalesByVendor'), SalesByVendor) <= 0.80, Dual('Top 80%', 1),

    If(RangeSum(Peek('CumSalesByVendor'), SalesByVendor) <= 0.95, Dual('80-95%', 2), Dual('Bottom 5%', 3))) as Bucket

Resident TempTable

Order By SalesByVendor desc;

DROP Table TempTable;

View solution in original post

21 Replies
Anil_Babu_Samineni

Read this? May be helps to you

Buckets

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mrthomasshelby
Creator III
Creator III
Author

I have read that already but as I'm fairly new to Qlik Sense and don't know the commands used to create Pareto bins in the load script, it'd be helpful if you could give me a detailed recipe to create such bins in the load script. Thanks!

Anil_Babu_Samineni

Would you provide sample data?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mrthomasshelby
Creator III
Creator III
Author

Thanks for taking the time!

Let's say I have the following data. I'd like to classify them into 3 Pareto bins: Top 80%, 80-95%,Bottom 5%

Vendor       Sales

A              1300

B              2200

A              1400

B              2100

C              2500

A              300

C              900

D              3200

B              2000

D              1500

E              1100

F              3500

E              1500

G              1400

H              600

H              900

G             1200

sunny_talwar

May be this

Table:

LOAD * INLINE [

    Vendor,      Sales

    A,              1300

    B,              2200

    A,              1400

    B,              2100

    C,              2500

    A,              300

    C,              900

    D,              3200

    B,              2000

    D,              1500

    E,              1100

    F,              3500

    E,              1500

    G,              1400

    H,              600

    H,              900

    G,            1200

];

Left Join (Table)

LOAD Sum(Sales) as TotalSales

Resident Table;

TempTable:

LOAD Vendor,

  Sum(Sales)/TotalSales as SalesByVendor

Resident Table

Group By Vendor, TotalSales;

Left Join (Table)

LOAD Vendor,

  RangeSum(Peek('CumSalesByVendor'), SalesByVendor) as CumSalesByVendor,

  If(RangeSum(Peek('CumSalesByVendor'), SalesByVendor) <= 0.80, Dual('Top 80%', 1),

    If(RangeSum(Peek('CumSalesByVendor'), SalesByVendor) <= 0.95, Dual('80-95%', 2), Dual('Bottom 5%', 3))) as Bucket

Resident TempTable

Order By SalesByVendor desc;

DROP Table TempTable;

Anil_Babu_Samineni

You are the boss, How come you handle all stuff with in the Time only. I am trying same.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

There a secret sauce

mrthomasshelby
Creator III
Creator III
Author

You are the best! Thanks a lot!

mrthomasshelby
Creator III
Creator III
Author

I also have a small clarification if you don't mind. Now let's say I have another field: Type. The data is now as follows:

Vendor       Sales    Type

A              1300        C

B              2200        N

A              1400        N

B              2100        N

C              2500        C

A              300          N

C              900          C

D              3200        N

B              2000        C

D              1500        C

E              1100        N

F              3500        C

E              1500        C

G              1400        N

H              600          C

H              900          N

G             1200         C


Now I'd like to apply a filter on Type. Let's say I apply the filter as 'C'. Now will the buckets be created on fields which only have the type C or will it still be created on all the fields? If it is still on the overall data, how can I make it create buckets only on the fields which have type as 'C'? Is it possible at all?


Thanks a lot again! You are a life saver! Much much appreciated!