Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Here is an example of the data (3 excel files lookup, Actual and Portfolio) and output, can anyone help me on this?
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 ];
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
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 ];