Matching with '*'

[daniel_kusiak]

Hi Community,

I'm trying to solve this problem but without any luck. I have tables like below:

First i have my data:

1:

Wkey	k_pier	k_doc	pod_f	msp_f	Desc
DD1*	D	D	1	*	aaa
DP*1	D	P	*	1	bbb
PH3*	P	H	3	*	ccc
PH*0	P	H	*	0	ddd

So, the Wkey field = k_pier&k_doc&pod_f&msp_f

Now i have a second and third table for pod_f and msp_f. In those tables data look like this:

2:

pod_f	pod
*	1
*	2
*	3
*	4

3:

msp_f	msp
*	0
*	1

What i'm trying to achive?

4:

Wkey	Nkey	Header 3
DD1*	DD10	aaa
DD1*	DD11	aaa
DP*1	DP11	bbb
DP*1	DP21	bbb
DP*1	DP31	bbb
DP*1	DP41	bbb
PH3*	PH30	ccc
PH3*	PH31	ccc
PH*0	PH10	ddd
PH*0	PH20	ddd
PH*0	PH30	ddd
PH*0	PH40	ddd

As You can see each time there is a '*' in pod_f or msp_f  in Table 1 it is replaced with every entry from Tables 2 or 3 and the proper combination is created in table 4 (Nkey) and it has a matching description form Table1.

Thanks in advance for your help 🙂

[avkeep01 · Accepted solution]

Hi Daniel,

This is the script you could use:

table1:
LOAD * INLINE [
Wkey, k_pier, k_doc, pod_f, msp_f, Desc
DD1*, D, D, 1, *, aaa,
DP*1, D, P, *, 1, bbb,
PH3*, P, H, 3, *, ccc,
PH*0, P, H, *, 0, ddd,
];

table2:
LEFT JOIN (table1) LOAD * INLINE [
pod_f, pod
*, 1
*, 2
*, 3
*, 4
];

table3:
LEFT JOIN (table1) LOAD * INLINE [
msp_f, msp
*, 0
*, 1
];


temp01_result:
LOAD
Wkey,
k_pier&k_doc&IF(pod_f<>'*',pod_f,pod)&IF(msp_f<>'*',msp_f,msp) as Nkey,
k_pier,
k_doc,
IF(pod_f<>'*',pod_f,pod) AS pod_f,
IF(msp_f<>'*',msp_f,msp) AS msp_f,
Desc
RESIDENT table1;
DROP TABLE table1;

I've attached the app as example.

[daniel_kusiak]

That's it! Thank you.