how to match comma separated values ?

[cruzo008]

Hi,

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

[Shubham_Deshmukh]

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;

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

Regards,

Shubham

[sunny_talwar · Accepted solution]

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
];