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

[Anonymous]

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!

[sasiparupudi1]

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

[Anonymous]

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!

[Digvijay_Singh]

Can you share expected output?

[Anonymous]

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.

[qliksus]

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), '-' )

[Anonymous]

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

[Anonymous]

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!

[qliksus]

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

[qliksus]

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