Applymap

[maniram23]

Hi Experts,

I am trying to do an applymap like this .

But i am getting error of Field is not found(Pcflag)

I wrote like this

T1:

Mapping

LOAD Filterid,

     Rfsid

FROM

(ooxml, embedded labels, table is Sheet1);

FOR  Each vsheet in 'Sheet2','Sheet3'

T2:

LOAD *,

  ApplyMap('T1',Filterid&Pcflag) as app

FROM

(ooxml, embedded labels, table is $(vsheet));

next vsheet;

Here i am having Sheet2 in Filterid and Pcflag .  Sheet3 only Filterid is available.

please give me an idea.

[avinashelite]

try like this

T1:

Mapping

LOAD Filterid,

     Rfsid

FROM

(ooxml, embedded labels, table is Sheet1);

FOR  Each vsheet in 'Sheet2','Sheet3'

T2:

LOAD *,

  ApplyMap('T1',Filterid,'Un-know') as app

FROM

(ooxml, embedded labels, table is $(vsheet));

next vsheet;

[Kushal_Chawda]

You are doing wrong applymap. how you are mapping id in other table? Use sheet2 and sheet3 table as mapping table and perform apply map in T1

[marcus_sommer]

You need to react on which sheet you are loading, maybe in this way:

FOR  Each vsheet in 'Sheet2','Sheet3'

let vMapCriteria = if('$(vsheet)' = 'Sheet2', 'Filterid&Pcflag', 'Filterid');

T2:

LOAD *,

  ApplyMap('T1',$(vMapCriteria), '#NV') as app

FROM

(ooxml, embedded labels, table is $(vsheet));

- Marcus

[maniram23]

Hi  Experts,

Here my mapping table is  like this

Filterid	Rfsid
Rf01Pc01	Rat111
Rf02Pc02	Rat222
Rf03Pc03	Rat333
Cf01	ABT1
Cf02	ABT2
Cf03	ABT3

and my Sheet2 and Sheet3 tabs are like this

Filterid	Pcflag	Name
Rf01	Pc01	Akash
Rf02	Pc02	Avinash
Rf03	Pc03	Srinu

and

Filterid	Name
Cf01	Raj
Cf02	Raja
Cf03	Vivek

the combination of these two tabs . i want to do an applymap in a single load  load_statement.

The final output is like this

Filterid	Rfsid	Name
Rf01Pc01	Rat111	Akash
Rf02Pc02	Rat222	Avinash
Rf03Pc03	Rat333	Srinu
Cf01	ABT1	Raj
Cf02	ABT2	Raja
Cf03	ABT3	Vivek

[avinashelite]

some thing like this

T1:

Mapping

LOAD Filterid,

     Rfsid

FROM

(ooxml, embedded labels, table is Sheet1);

FOR  Each vsheet in 'Sheet2','Sheet3'

T2:

LOAD *,

  if($(vsheet)='Sheet2',ApplyMap('T1',Filterid&Pcflag,'Un-know'),ApplyMap('T1',Filterid,'Un-know')) as app

FROM

(ooxml, embedded labels, table is $(vsheet));

next vsheet;

[marcus_sommer]

A check if a field is available couldn't be done from inside a load-statement. It will always require a outside-logic like in my example above and storing it in a variable or creating a load-statement on the fly.

- Marcus

[avinashelite]

sorry, I didn't tested this , thanks for correcting me marcus_sommer‌