linking many to many

[surajap123]

Hi Experts,

I have a table of data.

I want to keep only those Products that belong to 'Art' Category and remove rest of the products from the dashboard ie. Product-'Newell 322' in this case.

All the Art products belong to Sector A1 and A2, as products and categories have many-to-many relationships.

My Requirement is, if a user select on Category 'Art', the sector A1 and A2 should be associated ie not greyed out.

Please help!

[sunny_talwar · Accepted solution]

May be try this

Data:

LOAD 'Art' as InScope,

Category,

    Product

FROM

[many to manyData.xls]

(biff, embedded labels, table is Sheet1$)

Where Category = 'Art';

Left Join (Data)

LOAD * INLINE [

    Sector

    A1

    A2

];

Concatenate (Data)

LOAD if(WildMatch(Category,'B*'),'A1',

if(WildMatch(Category,'P*'),'A2')) as Sector,

if(Category = 'Art','Art') as InScope,

Category,

    Product

FROM

[many to manyData.xls]

(biff, embedded labels, table is Sheet1$)

Where Category <> 'Art' and Exists(Product);

[stabben23]

Hi,

try With this code

Data:
LOAD if(WildMatch(Category,'B*'),'A1',
if(WildMatch(Category,'P*'),'A2')) as  Sector,
//if(Category = 'Art','Art') as InScope,
  Category as InScope,
Product
FROM
[.\many to manyData.xls]
(biff, embedded labels, table is Sheet1$)
where Category ='Art';

Why they dont get "assosiated " is because both InScope and Sector is created from Category With different if statment.

[surajap123]

Thanks Sunny.

There is a issue here. When a user click on a products only corresponding Sectors should be associated like in the initial app.

eg- If user select product-  'Acco Six-Outlet Power Strip, 4' Cord Length', then only Sector A2 should be associated.

Thanks!

[surajap123]

Hi Staffan,

The sector field is not generated with data, due to where condition.

[sunny_talwar]

But didn't you say that Art is associated with both Sectors? I am confused

[surajap123]

Sorry for the confustion.

I want to keep only the products that belong to categoy Art, which is working fine.

As you can see the relation between Products and Category is many-to-many.

So, if a user select  a specific product only specific sector should be associated that meet the condition.

if(WildMatch(Category,'B*'),'A1',

   if(WildMatch(Category,'P*'),'A2'))      as Sector

[sunny_talwar]

May be using Triggers? Is that an option?

[surajap123]

I think i am confused too..

I will try to work on your solution and get back. Thanks!

[sunny_talwar]

I guess, I was asking if we can make this work using Triggers.... will that work for you or are you looking to avoid triggers?