Load statement to describe stores

[#1]

Hi guys,

I hope there is a hero among you that can save me!

I've attached an example of my attempt for you to laugh at and hopefully will give you a better idea of what we need to do.

We need to describe all the stores in a database, based on whether or not they sold a particular product.

Sounds simple right?! lol I have highlighted the list boxes which are critical to our success in red.

1) In the example attached: we need all the stores that sold Products that start with 'Air' to have a column that describes them ("Yes" in the example) I also need stores that didn't sell the product to be described as "No". It is very important that we can choose these stores and Sales for ALL the products are still seen, not just the specific "Air" products we are testing for (describing the store as an "Air Store"). The example works for the "Yes" stores, however it fails to accurately show the "No" stores. It is supposed to show Only store B (as well as the sales of ALL the brands for Store B, not just the "Air" brands.

2) We also need the stores to be described by the number of times they sold the specific "Air" product. In the example this is also not working correctly. Again it is meant to describe the store, and be an option to select i.e. Stores that sold 2 "Air" products also sold xyz. At the moment the example only shows the "Air" sales of stores that sold "Air" products x number of times, where it should show sales of ALL brands in stores that sold "Air" x number of times.

I hope this is described sufficiently.

I am desperately seeking a solution to this problem, any help would be hugely appreciated!

Thank you!

[its_anandrjs]

You can try to write like

==============

Table:

LOAD *,if(left(Product,3)='Air','Yes','No') as BuyAir;

LOAD *  INLINE [

    Customer, Product, NetSales

    A, Air Nike, 12

    A, Air Nike, 24

    A, Puma, 35

    A, Air Nike, 23

    A, Puma, 52

    A, Adidas, 26

    B, Puma, 24

    B, Puma, 25

    B, Reebok, 24

    B, Reebok, 34

    C, Air Jordan, 34

    C, Air Jordan, 56

    C, Air Nike, 78

    C, Reebok, 23

];

Load

  Customer,

  Product,

  if(left(Product,3)= 'Air',count(Customer),sum(0)) as FlagAir

Resident Table

Group By Customer, Product;

[#3]

Unfortunately this doesn't describe the stores and show the sales of the other brands. When I select stores that sold x product twice (ie: 2) from FlagAir, it must show all the brands sales in those stores. Your example only shows the "Air" sales.

Similarly when I select 0 times, it should only show store B.

Thanks for your reply, I do appreciate it.

[its_anandrjs]

Can you elaborate more with small example with data also.

[#5]

1) I need to show the stores and all the brands that have sold in that store based on whether or not a store ever sold an "Air" product

2) I need to show the stores and all the brands that have sold in that store based on how many times an "Air" product was sold in that store.

[#6]

Hi again,

See the example with the desired out put. Obviously I need to do this without having the data in the last two columns.