Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
daniel_kusiak
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
avkeep01
Partner
Partner

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
avkeep01
Partner
Partner

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

daniel_kusiak
Creator II
Creator II

That's it! Thank you.