Dynamic Mapping

[#1]

Hi All,

I have a Inline as:

Load * Inline

  [

  ID, Name

  1, Company

  2, Location

  3, Department

  4, Job Code

  5, Reservation

  6, Reports to

  ];

Second Inline like:

Load * Inline[

Company, Location, Department, Job Code, Reservation, Reports to

101, CA, IT, 101, Reserved, 10121

102, GA, IT, 102, Reserved, 12345

];

I want the result table like:

Second inline columns should be replaced by the id values which mentioned in first inline.

1	2	3	4	5	6
101	CA	IT	101	Reserved	10121
102	GA	IT	102	Reserved	12345

Please reply asap if any know.

Thanks,

Monika

[marcus_sommer]

Try this:

MapTab:

Mapping Load Name, ID Inline

  [

  ID, Name

  1, Company

  2, Location

  3, Department

  4, Job Code

  5, Reservation

  6, Reports to

  ];

Second Inline like:

Load * Inline[

Company, Location, Department, Job Code, Reservation, Reports to

101, CA, IT, 101, Reserved, 10121

102, GA, IT, 102, Reserved, 12345

];

rename fields using MapTab;

- Marcus

[#3]

Hi,

I don't want to rename the fields because the sequence of Id's in MapTab can change any time. Is there a way to make that dynamic.

Thanks!!

[marcus_sommer]

Generally these mapping and rename is dynamically. But you will have always to be ensure that changes to table1 would be reflected by changes to table 2 then otherwise it would not be matched properly.

- Marcus

[its_anandrjs]

Hi,

You can do this way but with excel file if possible and in excel sequence will be Name,ID and load this table and then in transformation transpose this file. Here i use the sample file Book2.xlsx as sample load you can try this code

ExcelTable:

LOAD

     NUM(Company) as Company,

     NUM(Location) as Location,

     NUM(Department) as Department,

     NUM([Job Code]) as [Job Code],

     NUM(Reservation) as Reservation,

     NUM([Reports to]) as [Reports to]

FROM

[Book2.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 1)),

Transpose()

));

Concatenate

LOAD * INLINE [

    Company, Location, Department, Job Code, Reservation, Reports to

    101, CA, IT, 101, Reserved, 10121

    102, GA, IT, 102, Reserved, 12345

];

Regards

Anand