Map 1 column to 4 different colums

[pauldamen]

All,

I have 2 excel sheets, 1 excels sheet contains the columns: ID1, ID2, ID3, ID4

The other Excel sheets contains 2 columns, Referencenumber, IDnumber

In the second Excel the column with the ID number can contain a number which can be in either 4 columns in the first excel.

How can I match the referencenumber in the second excel to a number which is in one of the ID colums. I have attached a sample Excel with the 2 sheets for the 2 excels.

Regards, Paul

[Gysbert_Wassenaar]

Perhaps like this:

MapRef:
MAPPING LOAD F2, Reference
FROM [.....xlsx] (ooxml, embedded labels, table is [ID number]);

TotalID:
LOAD [ID 2],
     [ID 3],
     [ID 4],
     [ID 5],
     [ID 6],
     ApplyMap('MapRef',[ID 2]) as [Reference 2],
     ApplyMap('MapRef',[ID 3]) as [Reference 3],
     ApplyMap('MapRef',[ID 4]) as [Reference 4],
     ApplyMap('MapRef',[ID 5]) as [Reference 5],
  ApplyMap('MapRef',[ID 6]) as [Reference 6]                      
FROM [.....xlsx] (ooxml, embedded labels, table is TotalID);

[pauldamen]

No luck..., thing is all should be mapped to 1 field because I need to be able to search a reference number and find the corresponding ID's, 1 reference number can have multiple ID's

Regards, Paul

[Gysbert_Wassenaar]

Can you give an example? Because I have no idea anymore what should be mapped to what and which conditions determine the match.

[CELAMBARASAN]

Hi Paul,

You could kind of the below by forming link table

Ref:

LOAD Reference,

     ID

FROM

Sampledoc.xlsx

(ooxml, embedded labels, table is [ID number]);

ID:

LOAD RowNo() AS UniqueNumber,

  [ID 2],

     [ID 3],

     [ID 4],

     [ID 5],

     [ID 6]

FROM

Sampledoc.xlsx

(ooxml, embedded labels, table is TotalID);

RefID_Link:

LOAD

  UniqueNumber,

  [ID 2] AS ID

Resident ID Where len(trim([ID 2]));

Concatenate

LOAD

  UniqueNumber,

  [ID 3] AS ID

Resident ID Where len(trim([ID 3]));

Concatenate

LOAD

  UniqueNumber,

  [ID 4] AS ID

Resident ID Where len(trim([ID 4]));

Concatenate

LOAD

  UniqueNumber,

  [ID 5] AS ID

Resident ID Where len(trim([ID 5]));

Concatenate

LOAD

  UniqueNumber,

  [ID 6] AS ID

Resident ID Where len(trim([ID 6]));

Thanks

Celambarasan

[awhitfield]

Hi Paul,

does the attached help at all?

Andy

[pauldamen]

Hi Andy,

This is exactly what I mean, I new problem though. The ID's have a logical path, so I need to have a hierarchy from ID2 -> ID3 -> ID4 etc.

By making a crosstable I lose the ability to create a group that has the hierarchy in it. Any ideas?