topic Re: linking many to many in QlikView

linking many to many

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: linking many to many

sunny_talwar — Fri, 09 Mar 2018 12:18:35 GMT

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

];

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);

Re: linking many to many

stabben23 — Fri, 09 Mar 2018 12:20:05 GMT

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.

Re: linking many to many

surajap123 — Fri, 09 Mar 2018 12:39:41 GMT

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!

Re: linking many to many

surajap123 — Fri, 09 Mar 2018 12:41:23 GMT

Hi Staffan,

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

Re: linking many to many

sunny_talwar — Fri, 09 Mar 2018 12:48:57 GMT

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

Re: linking many to many

surajap123 — Fri, 09 Mar 2018 13:04:16 GMT

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

Re: linking many to many

sunny_talwar — Fri, 09 Mar 2018 13:11:09 GMT

May be using Triggers? Is that an option?

Re: linking many to many

surajap123 — Fri, 09 Mar 2018 13:24:46 GMT

I think i am confused too..

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

Re: linking many to many

sunny_talwar — Fri, 09 Mar 2018 13:34:07 GMT

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?

Re: linking many to many

surajap123 — Fri, 09 Mar 2018 13:56:17 GMT

Yes Triggers are fine.

Please help me.

Re: linking many to many

surajap123 — Mon, 12 Mar 2018 10:56:34 GMT

Thanks for your help.