Applymap- fetching more than 1 possible value

[divya_anand]

Hi,

This is the data that I have-

When I try to use applymap, I am only able to fetch the first date it finds (I understand this is how applymap works).

But I would like to fetch all the data i.e all the corresponding date,hours etc for a particular code.

How can I do this?

A sample qvw is attached below, kindly let me know in case more explanation is needed to understand the problem.

Thank you.

[sunny_talwar]

In this case you might need to have a more complex key which can uniquely identify each row. For instance code and desc may be? Otherwise, how else will you know when hour is associated with which combination or date and code.

Data:

LOAD * INLINE [

code,date,hours,desc,type

111,1/20/2015,2,bskjsdbcj,A

111,2/25/2015,3,alknlkd,B

111,1/22/2015,1,iusdhjk cn,C

112,2/2/2015,4,ajnklan,A

112,2/2/2015,8,ksdjck,C

113,1/10/2015,7,isjkjde,A

113,3/20/2015,3,lslkdcslk,B

114,2/17/2015,10,uwjskjnc,B

];

Map:

Mapping

LOAD code&desc,

  date

Resident Data;

Data2:

LOAD ApplyMap('Map',code&desc) as map_date,

code,

hours

resident Data;

DROP Table Data;

[divya_anand]

Hi Sunny,

Thank you for the response.

I understand the logic. But, apart from having to create a key.. especially because desc could be a long text... is there a workaround for this?

As in, each record is going to be unique so do you think rowno() is another option?

[sunny_talwar]

RowNo() can be used... or use another field which might make it unique. I don't know what you are trying to do... but you have to make the Mapping table to be unique, else this is not going to work... you can do a left join... but then you are going to multiple the number of rows. I don't think that is what you want here.

[divya_anand]

True... not looking for multiple number of rows!

I'll give it a try and get back.

Thank you very much. You've always been very active and helpful in here.

[MarcoWedel]

can you please describe your expected result.

From what I understood, you are looking for something like.

Data:
LOAD * INLINE [
code,date,hours,desc,type
111,1/20/2015,2,bskjsdbcj,A
111,2/25/2015,3,alknlkd,B
111,1/22/2015,1,iusdhjk cn,C
112,2/2/2015,4,ajnklan,A
112,2/2/2015,8,ksdjck,C
113,1/10/2015,7,isjkjde,A
113,3/20/2015,3,lslkdcslk,B
114,2/17/2015,10,uwjskjnc,B
];

Data2:
LOAD distinct
          date as map_date,
          code,
          hours
resident Data;

without the need for any ApplyMap at all.

regards

Marco