Help with Mapping

[jyothish8807]

Hi All,

I have a data in below format:

Manager	Report
A	1
A	2
A	3
B	1
B	2
B	3
C	1
C	2
C	3

Now i want to create a new filed which should map to both Manager and Report separately. The expected output association should be like this:

Thanks in advance !

@jagan @sunny_talwar

NewField	Mapto
AA	A
BB	B
CC	3

[sunny_talwar]

Can you explain how you are using the input data to reach at the output?

[Gabriel]

Hi,

Am not sure I understood you well, but try below script

Map_Text:
MAPPING LOAD * INLINE [
MapFrom, MapTo
AA,AA
BB,BB
CC,CC
];

tmp:
LOAD *,MAPSUBSTRING('Map_Text',Manager) AS [Map Result] INLINE [
Manager, Report
A, 1
A, 2
A, 3
B, 1
B, 2
B, 3
C, 1
C, 2
C, 3
](DELIMITER IS ',');

Hope it helps

[jyothish8807]

Hi Sunny,

In my case below is my Fact:

Fact:

Manager	Report	Sale
A	1	12
A	2	34
A	3	333
B	1	34
B	2	546
B	3	44
C	1	56
C	2	67
C	3	28

Now i want to create a new Filed (a Group filed which is combination of Multiple Managers and Report at same time)

So, the newFiled should have values "AA","BB",'CC" for example.

When i click on AA, then Manager A should be selected.

When  BB is selected manager B should be selected. (These two association can be build with simple mapping file)

But when i select CC, it should select '3' from Report filed and not associated to Manager field at all. 

Hope i am able to explain you the scenario 🙂

Thanks!

[jyothish8807]

Hi Gabriel,

Thanks for your help.

I have tried to explain the scenario again, hope it helps to understand the requirement.

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD RowNo() as RowNum,
	 *;
LOAD * INLINE [
    Manager, Report, Sale
    A, 1, 12
    A, 2, 34
    A, 3, 333
    B, 1, 34
    B, 2, 546
    B, 3, 44
    C, 1, 56
    C, 2, 67
    C, 3, 28
];

LinkTable:
LOAD RowNum,
	 'AA' as Mapto
Resident Table
Where Manager = 'A';

Concatenate (LinkTable)
LOAD RowNum,
	 'BB' as Mapto
Resident Table
Where Manager = 'B';

Concatenate (LinkTable)
LOAD RowNum,
	 '3' as Mapto
Resident Table
Where Report = '3';

[jyothish8807]

Thanks for the solution 🙂 Will give it a try tomorrow with my real scenario, i might have to play around with few more dimension tables.

This should work 🙂