topic Re: Create Linktable with concatenate or join? in QlikView

Create Linktable with concatenate or join?

Mon, 18 Jul 2011 13:58:11 GMT

I have a table looking like this

PROJECT

idproject	product
1	;prod1;prod2;
2	;prod2;
3	;prod2;
4	;prod1;prod3;
5	;prod3;

I have now want to make a table looking like this

idproject	product
1	;prod1;
1	;prod2;
2	;prod2;
3	;prod1;
4	;prod1;
4	prod3;
5	prod3;

This table will then be a linktable between project and a table looking like this:

product	ProduktName
;prod1;	Window
;prod2;	Locker
;prod3;	Stone

I have tried to create the linktable by concatenating tables from PROJCET like this:

LinkProduct:

Load idproject,

product as ProductKey

Resident product like '*;Prod1;*'

Concatenate (LinkProduct)

Load idproject,

product as ProductKey

Resident product like '*;Prod2;*'

Concatenate (LinkProduct)

Load idproject,

product as ProductKey

Resident product like '*;Prod3;*'

But this will not create one uniqe row for each product.

Someone that could help

Create Linktable with concatenate or join?

Mon, 18 Jul 2011 14:23:14 GMT

Use JOIN, if Product Already exists, then It will do a JOIN and If Dosenot it will Add a New Entry in the link Table.

Re: Create Linktable with concatenate or join?

Tue, 19 Jul 2011 06:51:17 GMT

If i do a join it will not work. A join will give me a linktable looking like the old table:

Re: Create Linktable with concatenate or join?

Thu, 21 Jul 2011 14:50:55 GMT

Anyone pleas help with me with this problem.

It should not be so complex.

I need to create a table where I can choose from one of the products and if this product is presented in a project that project should be included.

For example pressing Window (prod1) in the table above should select the project with id 1 and 4 since these projects have the product Window.

Re: Create Linktable with concatenate or join?

Thu, 21 Jul 2011 15:16:46 GMT

Hi Samir,

Take a look at my example.

I hope is what you want.

Best regards.

Re: Create Linktable with concatenate or join?

Fri, 22 Jul 2011 07:01:17 GMT

This look very good.

But as I look into the code it looks like you only can handle 3 product, or am I wrong? I have total 10 procucts.

Maybe if you look at this pic you vill understand how I think.

Re: Create Linktable with concatenate or join?

Fri, 22 Jul 2011 07:42:38 GMT

Hi Samir,

The code will work fine for all products you have.

Please have a look again.

Best Regards.