topic Connect Tables by two columns in Connectivity & Data Prep

Connect Tables by two columns

rav3k — Wed, 17 Apr 2024 10:36:38 GMT

Hello

I have problem with synth keys. Im trying to solve it by preparing key but it won't work.

Here's my data:

LOAD
ORDER_NO as PROD_ORDER_NO,
SEQ as QM_PROD_SK_SEQ,
TT_NO as PTT,
RESULTS1

RESULTS2

RESULTS3 ...

2nd table

ORDER_NO as PROD_ORDER_NO,
SEQ as QM_PROD_SK_SEQ,
TT_NO as PTT,
DIF_RESULTS1

DIF_RESULTS2

DIF_RESULTS3 ...

How to prepare a key which will connect PTT and PROD_ORDER_NO without syth key, so that i will still be able to use PTT or PROD_ORDER_NO filter?

Re: Connect Tables by two columns

BrunPierre — Wed, 17 Apr 2024 11:08:00 GMT

Given this scenario, force concatenation will suffice.

Table1:
LOAD
...

Concatenate

Table2:
LOAD
...

Re: Connect Tables by two columns

rav3k — Wed, 17 Apr 2024 11:33:07 GMT

I have a lot of simmilar tables but with totally different measurements and measurements results so i don't want to have all the data in one table.

Is there any other way to connect them? I was thinking about composition key but i dont know if i will be still able to use PTT and PROD_ORDER_NO filters. And i don't know how to create it.

Re: Connect Tables by two columns

BrunPierre — Wed, 17 Apr 2024 12:21:47 GMT

Are you using the filter in the script or in an expression?

Table1:
LOAD ORDER_NO&'-'&SEQ&'-'&TT_NO as %Key,
//ORDER_NO as PROD_ORDER_NO,
//SEQ as QM_PROD_SK_SEQ,
//TT_NO as PTT,
RESULTS1
RESULTS2
RESULTS3 ...

Table2:
LOAD ORDER_NO&'-'&SEQ&'-'&TT_NO as %Key,
//ORDER_NO as PROD_ORDER_NO,
//SEQ as QM_PROD_SK_SEQ,
//TT_NO as PTT,
DIF_RESULTS1
DIF_RESULTS2
DIF_RESULTS3 ...

Re: Connect Tables by two columns

rav3k — Wed, 17 Apr 2024 12:25:23 GMT

I want to select data by ORDER_NO or PTT or STT.

That's why i want also change TT_NO to PTT, because in other table TT_NO is STT no, not PTT no.

Re: Connect Tables by two columns

BrunPierre — Wed, 17 Apr 2024 13:56:56 GMT

Clarify "I want to select data by ORDER_NO or PTT or STT".

Re: Connect Tables by two columns

rav3k — Thu, 18 Apr 2024 05:27:45 GMT

In ready application i want to use "filters" to filtrate data that will be shown.

In filters i want to have "ORDER_NO" and "PTT" and "STT".

Re: Connect Tables by two columns

BrunPierre — Thu, 18 Apr 2024 07:50:59 GMT

You might find it intriguing to learn that consolidating measures into a single table enhances simplicity, consistency, and efficiency within your data model, resulting in improved analytics results.

I think that if you share your complete requirements at once, you'll receive more assistance.

However, you can obtain the values within the composite key using a number of string functions.

For example, using SubField(%Key,'-',1) would give values corresponding to ORDER_NO.