topic Re: Map 1 column to 4 different colums in QlikView

Map 1 column to 4 different colums

pauldamen — Mon, 23 Mar 2015 10:30:14 GMT

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

Re: Map 1 column to 4 different colums

Gysbert_Wassenaar — Mon, 23 Mar 2015 10:37:24 GMT

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);

Re: Map 1 column to 4 different colums

pauldamen — Mon, 23 Mar 2015 10:46:00 GMT

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

Re: Map 1 column to 4 different colums

Gysbert_Wassenaar — Mon, 23 Mar 2015 10:52:40 GMT

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

Re: Map 1 column to 4 different colums

CELAMBARASAN — Mon, 23 Mar 2015 11:02:15 GMT

Hi Paul,

You could kind of the below by forming link table

Ref:

LOAD Reference,

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

Re: Map 1 column to 4 different colums

awhitfield — Mon, 23 Mar 2015 11:02:48 GMT

Hi Paul,

does the attached help at all?

Andy

Re: Map 1 column to 4 different colums

pauldamen — Mon, 23 Mar 2015 11:07:16 GMT

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?