topic Re: Applymap one to many values in QlikView

Applymap one to many values

MK9885 — Sat, 16 Nov 2024 20:00:17 GMT

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.

Re: Applymap one to many values

dplr-rn — Thu, 26 Sep 2019 15:59:07 GMT

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

Please expand on the scenario a bit more.

Re: Applymap one to many values

MK9885 — Thu, 26 Sep 2019 16:07:35 GMT

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.

Re: Applymap one to many values

marcus_sommer — Fri, 27 Sep 2019 11:26:07 GMT

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

Re: Applymap one to many values

Brett_Bleess — Mon, 07 Oct 2019 21:32:59 GMT

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