Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
cruzo008
New Contributor II

how to match comma separated values ?

Hi,

Here is an example of the data (3 excel files lookup, Actual and Portfolio) and output, can anyone help me on this?Capture.JPG

 

1 Solution

Accepted Solutions

Re: how to match comma separated values ?

This might be another way... Actual and portfolio can be kept as separate tables or can be joined....

Lookup:
Mapping
LOAD SubField(code, ','),
	 name;
LOAD * INLINE [
    name, code
    abc, "a1,a2,a3,a4,a5"
    def, "a6,a7,a8"
    igk, "a9,a10,a11"
];

Actual:
LOAD *,
	 ApplyMap('Lookup', code, null()) as name;
LOAD * INLINE [
    code, value1, week
    a2, 1, 12
    a8, 2, 13
    a9, 3, 14
];

portfolio:
LOAD * INLINE [
    name, value2, week
    abc, 1, 12
    def, 3, 13
    igk, 1, 14
];
2 Replies
Shubham_Deshmukh
Valued Contributor

Re: how to match comma separated values ?

Hi @cruzo008 ,

I tried this and got the result as per your requirement,

 

map1:
Mapping Load * Inline [
name,code
abc,'a1,a2,a3,a4,a5',
def,'a6,a7,a8',
igk,'a9,a10,a11',
];

map2:
Load *,
ApplyMap('map1',name,'NA') as code
inline [
name ,value2 , week
abc,1,12
def,3,13
igk,1,14
];
NoConcatenate
map3:
Load code,name,value2,week as week1 Resident map2;
let vCode = code;

map4:
load * Inline [
code1, value1 ,week
a2,1,12	
a8,2,13
a9,3,14
]
Where WildMatch(code1, '*'&'$(vCode)'&'*'); 
drop Tables map3;

bn.png

 

 

 

Hi @sunny_talwar , I think there is optimized way to do it, please let us know.

Regards,

Shubham:smileyhappy:

Re: how to match comma separated values ?

This might be another way... Actual and portfolio can be kept as separate tables or can be joined....

Lookup:
Mapping
LOAD SubField(code, ','),
	 name;
LOAD * INLINE [
    name, code
    abc, "a1,a2,a3,a4,a5"
    def, "a6,a7,a8"
    igk, "a9,a10,a11"
];

Actual:
LOAD *,
	 ApplyMap('Lookup', code, null()) as name;
LOAD * INLINE [
    code, value1, week
    a2, 1, 12
    a8, 2, 13
    a9, 3, 14
];

portfolio:
LOAD * INLINE [
    name, value2, week
    abc, 1, 12
    def, 3, 13
    igk, 1, 14
];