Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
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.