Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
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.
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.
That's it! Thank you.