Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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 |
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.
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.
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
];
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;
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
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;
Thanks to all, It is working now.
Whats the wrong with my app previously why peek function did not work.