Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Honored Contributor II

Applymap one to many values

Hi,

Can we do applymap one to many?

I have 2 columns 

Column1 Column2

1                  ABC

2                  BCD

3                 DGE

4                  ABC

5                 XYZ

As far as I know, apply map takes first available value to map. So in this case it would consider Value 1 from Column1 as ABC & ignore Value 4 for Column2

Can we map both 1 & 4 to ABC?

 

@sunny_talwar 

Thanks.

Labels (2)
4 Replies
Highlighted
Partner
Partner

Re: Applymap one to many values

Not using applymap. You will need to use join or similar.

Please expand on the scenario a bit more.

Highlighted
Honored Contributor II

Re: Applymap one to many values

This is my excel table

Column1 Column2

1                  ABC

2                  BCD

3                 DGE

4                  ABC

5                 XYZ

 

Need to map to a Dimension table, not fact table..

In other table I have 

Load

Cust1,

Cust2,

Location,

Price,

Column2 and so on...

From .....;

If I do a left join using Column2 from my excel table to Dim table, it won;t be a perfect key or if I do apply map it takes first available value & ignores rest.

Anyhow I did left join & used Column1 but when I select a filter which is related to Column2, say Location..

IF I select location as NY in filter, the tables shows 2 values for ABC which is 1 & 4

But when I select 1, I still get 1 & 4 in my straight table. If I select 4 I get both values.

To adjust this I used if condition but I know there is a better way to do this.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Applymap one to many values

One way could be to combine your lookup-value with a counter-information but I assume that you don't want really to access a possible n-th value else that it should return all available return-values of a lookup-value. In this case you could just apply an aggregation in the mapping table, like:

m: mapping load LookupValue, concat(ReturnValue, ' & ') from Source group by LookupValue;

- Marcus

Highlighted
Digital Support
Digital Support

Re: Applymap one to many values

About the best I have is a point to the Design Blog area to have a look at the posts there to see if you can find something that may fit your use case there.  I am putting the ApplyMap one directly below, but I am providing the base link for you after that where you can do your own searching to see if you can find some other examples on how you might approach things.

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

There are at least a few hundred posts out here these days, mostly how-to etc., so hopefully this will be a good resource for you.  Sorry I am not more help.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.