topic Re: m to n relationship mapping problem (only map first corresponding value) in QlikView

m to n relationship mapping problem (only map first corresponding value)

Anonymous — Thu, 19 Apr 2018 09:38:52 GMT

Hi everyone,

I have a problem on trying to calculate this:

So, I have three tables.

TABLE 1:

id CompanyID Product Value

1 c_01 a 100

2 c_02 b 200

3 c_03 c 300

TABLE 2:

CompanyID Businessline1 Businessline2 Businessline3

c_01 b01 b02 b03

c_02 b01 b04 b05

c_03 b02 b06 b07

Table3:

ProductID Businessline

a b01

a b04

a b03

b b11

b b12

c b01

c b02

c b04

The nature of cardinality of table 3 is many to many, where one product ID can belong to more than 1 business line, and one business line can be linked to more than 1 product ID. My goal is to give label on the table 1, by checking the corresponding productID with the company business line in table 2. If the businessline of companyID in table 1 matched with the businessline of productID in table 3, then I will put label in table1.

In the beginning, I tried to use calculated field to do this, and I managed to define the label. However, since it is in the visualisation, I was not able to calculate how many matching are there in the table one, and what is the total value of it.

Another option is doing it in the loading script, by join Table 1 and Table 2, but I could not label it, because I need table three to create calculated field in the data loading script. I tried to use apply map, but it is not working since it will only picked the first corresponding value in the table3, not all of the possible map (from productID to Businessline). Do you have any suggestion on how to solve this kind of problem?

Thanks!

Re: m to n relationship mapping problem (only map first corresponding value)

sasiparupudi1 — Thu, 19 Apr 2018 14:06:46 GMT

May be do a left join of table3 to table 2 and based on the composite key o productid and company id, create an apply map.

use the applymap to check if you get a value out in table 1

hth

Re: m to n relationship mapping problem (only map first corresponding value)

Anonymous — Tue, 24 Apr 2018 20:50:37 GMT

Sorry for late reply. I am really new here so I do not notice the notification. would you mind explain what you mean by left join table 3 and table 2? because there is several field with same business id in the table 2 so i am a bit confused.

Thanks!

Re: m to n relationship mapping problem (only map first corresponding value)

Digvijay_Singh — Tue, 24 Apr 2018 23:24:57 GMT

Can you share expected output?

Re: m to n relationship mapping problem (only map first corresponding value)

Anonymous — Wed, 25 Apr 2018 20:11:00 GMT

I am expecting output like this :

TABLE 1:

id CompanyID Product Value Label

1 c_01 a 100 1

2 c_02 b 200 0

3 c_03 c 300 1

label "1" here means that product a are shipped by a company with a corresponding business line. We can see here that company c_01 have three business line, namely b01, b02, b03 , while product a corresponds to business line b01, b04 and b03. As there is at least one matching business line between company and product, then I want to give it label 1.

For the the second row in table 1, the label is 0 because there is no matching business line between company and product, as shown in table 2 and table 3.

Re: m to n relationship mapping problem (only map first corresponding value)

qliksus — Fri, 27 Apr 2018 05:42:33 GMT

connect the companyId and Prodcut separately in the script and use the below as expression

PurgeChar( SubStringCount( concat(Businessline,',') ,Businessline1) or

SubStringCount( concat(Businessline,',') ,Businessline2) or

SubStringCount( concat(Businessline,',') ,Businessline3), '-' )

Re: m to n relationship mapping problem (only map first corresponding value)

Anonymous — Mon, 30 Apr 2018 20:30:28 GMT

What do you mean by connect it separately? I do not understand

Re: m to n relationship mapping problem (only map first corresponding value)

Anonymous — Mon, 30 Apr 2018 20:36:37 GMT

I tried to use this, but i do not understand the purgechar part here, would you mind explain it to me? because i thought we can only use purgechar if we want to clean the field from garbage/unnecessary symbols. thanks!

Re: m to n relationship mapping problem (only map first corresponding value)

qliksus — Wed, 02 May 2018 08:22:45 GMT

Sorry for the delayed response

the purgechar is used to remove the '-' which will get generated during the comparison i.e when I check a condition A=B it will return -1 when the condition is true and 0 when its false . Now to remove this '-' I used purge char to remove it

Re: m to n relationship mapping problem (only map first corresponding value)

qliksus — Wed, 02 May 2018 09:18:12 GMT

What I mean is u need to have the below datamodel and use the expression I suggested and let me know