Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Applymap in 1-many situation

Hi All,

I have read the below statement-

Whenever there is a one to many relationship between tables then a separate table must be loaded into the data model instead of using a mapping table and applymap.

Could you please explain(with a sample data model) me what is the issue, if we use applymap when there is one-many relationship??

Thanks !!

1 Solution

Accepted Solutions
Highlighted

Re: Applymap in 1-many situation

Hi Suraj, by default, if you link one to many in the same table, it's only possible duplicating records, and the way to avoid duplicate records is to load in different table.

By default applymap only returns one value (if there are many it returns only the first it finds), you can retrieve many using a mix of concat and subfield but I can't think in any usefull situation to make this, in a silly example it can be something like:

Data:

LOAD * INLINE [

Nº, client

1, Client 1

1, Client 1-2

2, Client 2

];

Map:

Mapping LOAD Nº, concat(client, '#') Resident Data Group by Nº;

Data2:

LOAD Subfield(ApplyMap('Map', Nº), '#') as Client,

    Sales;

LOAD * Inline [

Nº, Sales

1, 5000

2, 10000

];

DROP Table Data;

That will duplicate the sales for client Nº 1, it doesn't makes sense but is an example of using applymap with a one to many relationship.

View solution in original post

9 Replies
Highlighted
Master II
Master II

Re: Applymap in 1-many situation

Highlighted
Master II
Master II

Re: Applymap in 1-many situation

Highlighted
Partner
Partner

Re: Applymap in 1-many situation

Hi Suraj

ApplyMap will only map the first sorted value from your mapping table.

Imagine a table like this:

Key  Value

A      1

A      2

A      3

B      1

B      2

C      1

If you join your fact table with the above table, you will end up with duplicate values - for A your output will have the values:

A = 1,2 and 3

B = 1 and 2

C = 1 (as it only has one value).

ApplyMap however, will only return one value for each Key, which in the above example will return

A = 1

B = 1

C = 1

1 is returned as this is the first sorted value in your table.

This is actually brilliant when we are working wit facttables and want to avoid duplicate rows.

Hope this makes sense?

//Martin

Highlighted

Re: Applymap in 1-many situation

Hi Suraj, by default, if you link one to many in the same table, it's only possible duplicating records, and the way to avoid duplicate records is to load in different table.

By default applymap only returns one value (if there are many it returns only the first it finds), you can retrieve many using a mix of concat and subfield but I can't think in any usefull situation to make this, in a silly example it can be something like:

Data:

LOAD * INLINE [

Nº, client

1, Client 1

1, Client 1-2

2, Client 2

];

Map:

Mapping LOAD Nº, concat(client, '#') Resident Data Group by Nº;

Data2:

LOAD Subfield(ApplyMap('Map', Nº), '#') as Client,

    Sales;

LOAD * Inline [

Nº, Sales

1, 5000

2, 10000

];

DROP Table Data;

That will duplicate the sales for client Nº 1, it doesn't makes sense but is an example of using applymap with a one to many relationship.

View solution in original post

Highlighted
Creator II
Creator II

Re: Applymap in 1-many situation

Thanks everyone for the valuable information.

Highlighted
Not applicable

Re: Applymap in 1-many situation

Hi Ruben,

You written post on Applymap in 1-many situation

The above you have written for apply map(1 to many relation).How to solve the duplicate issue here(Thats where I am stuck)

Can you please help me on how to solve the duplicate sales values.??

your help is really appreciated...

Awaiting for your reply

Regards,

Jai

Highlighted

Re: Applymap in 1-many situation

Hi Jai, mine was a silly example of how make duplicates using applymap, if you use applymap alone, without the concat step in the Map table and without the Subfield function in Data2 table, applymap will get the first sorted value, and there will be no duplicate issue. If fact, one of the advantages to use applymap is to avoid duplicate issues.

If you have some issue you should open a new question, explaining your issue. Better if you give sample data, wich results you get wrong and what results are you expecting.

Me or others user online at that moment will help you to resolve your issue.

Highlighted
Not applicable

Re: Applymap in 1-many situation

Thanks for your Quick reply Rubin.

Issue is my table has one to many relationships(Same table)

for example NI-1004sku has some 5 duplicate sku mapped

Thats one of the reason I used your example script of Concat example.

However there are there ratios and Quantity against the above getting

duplicated.

Let me know,if you worked on similar situation and solved

Regards,

Jai

On Mon, Apr 6, 2015 at 9:51 PM, Ruben Marin <qcwebmaster@qlikview.com>

Highlighted

Re: Applymap in 1-many situation

Hi Jai,

A sku with 5 descriptions?, I think that each description should have his date range to apply or there must be something to difference or to unify the sku description for each (picking the last one?)

Anyway if you have to maintain that duplicates you'll need to load the relation in other table, as said in the beggining of my later post:

"if you link one to many in the same table, it's only possible duplicating records, and the way to avoid duplicate records is to load in different table."