Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Matching with '*'

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_pierk_docpod_fmsp_fDesc
DD1*DD1*aaa
DP*1DP*1bbb
PH3*PH3*ccc
PH*0PH*0ddd

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_fpod
*1
*2
*3
*4

3:

msp_fmsp
*0
*1

What i'm trying to achive?

4:

WkeyNkeyHeader 3
DD1*DD10aaa
DD1*DD11aaa
DP*1DP11bbb
DP*1DP21bbb
DP*1DP31bbb
DP*1DP41bbb
PH3*PH30ccc
PH3*PH31ccc
PH*0PH10ddd
PH*0PH20ddd
PH*0PH30ddd
PH*0PH40ddd

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 🙂

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Matching with '*'

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.

View solution in original post

2 Replies
Highlighted
Partner
Partner

Re: Matching with '*'

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.

View solution in original post

Highlighted
Creator II
Creator II

Re: Matching with '*'

That's it! Thank you.