topic Re: Applymap- fetching more than 1 possible value in QlikView

Applymap- fetching more than 1 possible value

divya_anand — Sun, 05 Feb 2017 17:13:37 GMT

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.

Re: Applymap- fetching more than 1 possible value

sunny_talwar — Sun, 05 Feb 2017 17:38:44 GMT

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;

Re: Applymap- fetching more than 1 possible value

divya_anand — Sun, 05 Feb 2017 17:46:10 GMT

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?

Re: Applymap- fetching more than 1 possible value

sunny_talwar — Sun, 05 Feb 2017 17:51:22 GMT

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.

Re: Applymap- fetching more than 1 possible value

divya_anand — Sun, 05 Feb 2017 17:57:14 GMT

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.

Re: Applymap- fetching more than 1 possible value

MarcoWedel — Sun, 05 Feb 2017 18:05:24 GMT

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