Applymap ()

[Anonymous]

Hi Friends,

I am to Forum and seeking help of the below scenario.

I would like to build table by using below 3 mapping tables.

Inputs:

=======

Input table:

a,

b,

c

from Input table;

Map_tab1:

a,

desc1

Map_tab2:

desc1,

desc2

Map_tab3

desc2,

desc3

Expected O/P Table:

Field1	Description
a	desc1
desc1	desc2
desc2	desc3

I used below script but sofar not succieded.

[AppToDesc]:

Load
a,
ApplyMap('Map_tab1',a,a as desc1,
ApplyMap('Map_tab2',Peek('desc1',,'AppToDesc1'),desc1) as desc2,

ApplyMap('Map_tab3',Peek('desc2',,'AppToDesc2'),desc2) as desc3,

Resident [Input table];
Here i am getting error message says that field 'desc1' not found.

Thanks,

JaswantC.

[jagan]

Hi Jaswant,

Can you attach some sample data/ file, it helps in understanding.  Seems that your script is wrong can you post entire script.

Regards,

Jagan.

[Anonymous]

Hi Jagan,

Please find attached app and i am looking below result from the app.

StateCode	Capital	County	StareetNM
MA	Boston	Framingham	GraniteST
CA	Sacramento	Quincy	Quincy
TX	Dallas	GreenVille	AdamsStreet

[Anonymous]

Hi Jagan,

Some how i am close to solution but i would like to get rid of duplicate records if i use distinct again i am not getting desired results.

[jagan]

Hi,

Please find the script below

Input:

LOAD * INLINE [
StateCode
MA
CA
TX
NH
];



Map_tab1:
Mapping LOAD * INLINE [
StateCode, Capital
MA, Boston
CA, Sacramento
TX, Dallas
];

Map_tab2:
Mapping LOAD * INLINE [
Capital, County
Boston, Framingham
Sacramento, Quincy
Dallas, Greenville
];

Map_tab3:
Mapping LOAD * INLINE [
County, StreetNM
Framingham, GraniteSt
Greenville, Admasstreet
];

FieldToStreet:
LOAD
*,
ApplyMap('Map_tab2',Peek('County'),County) as [StreetNM];
LOAD
*,
ApplyMap('Map_tab2', Capital,'N/A') as [County];
Load
StateCode as StateCode,
ApplyMap('Map_tab1',StateCode,'N/A') as [Capital]
Resident Input;


DROP TABLE Input;

Regadrs,

Jagan.

[Gysbert_Wassenaar]

Try

Map_tab1:
Mapping LOAD * INLINE [
    StateCode, Capital
     MA, Boston
     CA, Sacramento
     TX, Dallas
];


Map_tab2:
Mapping LOAD * INLINE [
    Capital, County
    Boston, Framingham
    Sacramento, Quincy
    Dallas, Greenville
   ];

Map_tab3:
Mapping LOAD * INLINE [
    County, StreetNM
    Framingham, GraniteSt
    Greenville, Admasstreet
];

Input:
LOAD
    StateCode
    , ApplyMap('Map_tab1',StateCode) as Capital
    , ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode)) as County
    , ApplyMap('Map_tab3',ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode))) as StreetNM   
INLINE [
    StateCode
    MA
    CA
    TX
    NH
];

[MayilVahanan · Accepted solution]

Hi

Try like this

Input:

LOAD * INLINE [

    StateCode

    MA

    CA

    TX

    NH

];

Map_tab1:

Mapping LOAD * INLINE [

    StateCode, Capital

     MA, Boston

     CA, Sacramento

     TX, Dallas

];

Map_tab2:

Mapping LOAD * INLINE [

    Capital, County

    Boston, Framingham

    Sacramento, Quincy

    Dallas, Greenville

   ];

Map_tab3:

Mapping LOAD * INLINE [

    County, StreetNM

    Framingham, GraniteSt

    Greenville, Admasstreet

];

FieldToStreet:

LOAD *, ApplyMap('Map_tab3',County,County) as [StreetNM];

LOAD *, ApplyMap('Map_tab2',Capital,Capital) as [County];

Load

StateCode as StateCode,

ApplyMap('Map_tab1',StateCode,StateCode) as [Capital]

Resident Input;

[jonathandienst]

Hi

I am not sure of your requirements, but to use nested applymaps, then do something like:

[AppToDesc]:

Load

  a,

  ApplyMap('Map_tab1', a) as desc1,

  ApplyMap('Map_tab2', ApplyMap('Map_tab1', a)) as desc2,

  ApplyMap('Map_tab3', ApplyMap('Map_tab2', ApplyMap('Map_tab1', a))) as desc3

Resident [Input table];

Regards

Jonathan

[#9]

  Use this code and run. Tried with Jonathan approach. It works fine if we create with nested applymap.

Input:

LOAD * INLINE [
StateCode
MA
CA
TX
NH
];



Map_tab1:
Mapping LOAD * INLINE [
StateCode, Capital
MA, Boston
CA, Sacramento
TX, Dallas
];

Map_tab2:
Mapping LOAD * INLINE [
Capital, County
Boston, Framingham
Sacramento, Quincy
Dallas, Greenville
];

Map_tab3:
mapping LOAD * INLINE [
County, StreetNM
Framingham, GraniteSt
Greenville, Admasstreet
];

FieldToStreet:
Load
StateCode as StateCode,
ApplyMap('Map_tab1',StateCode) as [Capital],
ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode)) as [County],
ApplyMap('Map_tab3',ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode))) as [StreetNM]
Resident Input;

DROP Table Input;

[Anonymous]

Thanks to all, It is working now.

Whats the wrong with my app previously why peek function did not work.