Mapping Composite Key for multiple combinations

[udit_kumar_sana]

Hi All,

I am facing a challenge to solve the below issue:

Suppose I have 2 tables called Table1 and Table2. I need to join them using a composite key containing 4 fields namely A, B, C and D.

Now the data is as follows:

There are rows in the table2 where one of the mapping fields has ‘*’ in it. This means that for that line in the table that field is ignored, the mapping is then done for all fields on a row with a  value other than ‘*’ in a field.

This can happen for more than 1 fields for a single record. For example:

TableName	FieldA	FieldB	FieldC	FieldD
Table1	1	2	3	4
Table2	1	*	3	4

Now for the above example, as FieldB has '*' as a value, key joining happens based on 1-3-4.

Similarly, it may so happen that '*' comes as a value for multiple fields.

How can this be implemented in Qlikview dynamically without hard coding '*' values?

Regards,

Udit

[sunny_talwar]

So you want to use * or you don't want? I am not 100% sure I understood your requirement?

[udit_kumar_sana]

I don't exactly want to remove * from the data, its just that the composite key should not use * while joining.

[sunny_talwar]

How are you creating it in the first place? Something like this?

FieldA & '-' & FieldB & '-' & FieldC & '-' & FieldD

May be do like this

If(FieldA <> '*', FieldA & '-') & If(FieldB <> '*', FieldB & '-') & If(FieldC <> '*', FieldC & '-') & If(FieldD <> '*', FieldD)

[udit_kumar_sana]

Hi Sunny,

Let me explain the above issue in detail:
Table1:
Load A, B, C, D, E, F from Table1;

Table2:
Load A, B, C, D, G, H from Table2;

Now I need to join these 2 tables using a composite key as follows:
Load A&'-'&B&'-'&C&'-'&D as Key,
     E, F from Table1,

Load A&'-'&B&'-'&C&'-'&D as Key,
     G, H from Table2,

But now, let us have a look at the data in table2:
A B C D
1 * 3 4
5 6 * 8
9 * 11 *
* 14 * *

Now my joining key as per the data should be like:
1-*-3-4
5-6-*-8
9-*-11-*
*-14-*-*

But I want it like:
1-3-4
5-6-8
9-11
14(logically '-' should be there if '*' is ignored, but it would be good if it can avoided)

Likewise, for joining, if we get a similar data in Table1 to join, 1-2-3-4, it should be modified to 1-3-4.

[sunny_talwar]

Here is one option

Table:

LOAD *,

  RowNo() as SNo,

  If(Right(If(A <> '*', A & '-') & If(B <> '*', B & '-') & If(C <> '*', C & '-') & If(D <> '*', D), 1) = '-',

  Left(If(A <> '*', A & '-') & If(B <> '*', B & '-') & If(C <> '*', C & '-') & If(D <> '*', D), Len(If(A <> '*', A & '-') & If(B <> '*', B & '-') & If(C <> '*', C & '-') & If(D <> '*', D))-1),

  If(A <> '*', A & '-') & If(B <> '*', B & '-') & If(C <> '*', C & '-') & If(D <> '*', D)) as Key,

  If(A <> '*', A & '-') & If(B <> '*', B & '-') & If(C <> '*', C & '-') & If(D <> '*', D) as TempKey;

LOAD * INLINE [

    A, B, C, D

    1, *, 3, 4

    5, 6, *, 8

    9, *, 11, *

    *, 14, *, *

];

[udit_kumar_sana]

This help me with the logic from table 2, but how can I remove corresponding value from table 1 composite key in order to join table.

For example:

Key is 1-2-3-4 in table 1.

&

Key in table 2 is 1-3-4.

how to remove 2 from Key of table 1.

[sunny_talwar]

Is this Key already available in Table2? If it is, then you can try Where Exists may be

Table2:

LOAD ....

From ...

Where not Exists(Key);

[udit_kumar_sana]

Hi Sunny,

This key is not available in table1 as it is. It may be available in the form of:

1-2-3-4 corresponding to 1-*-3-4 in table2

5-6-7-8 corresponding to 5-6-*-8 in table2

9-10-11-12  corresponding to 9-*-11-* in table2

13-14-15-16 corresponding to *-14-*-* in table2

we need to modify the key as per the '*' rejection from Table2.

[sunny_talwar]

This isn't very straight forward. I will have to test it out a little bit. I will try to come up with a solution if no one else do