Applymap in 1-many situation

[surajap123]

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 !!

[ecolomer]

See this:

http://community.qlik.com/message/676348#676348

[ecolomer]

And also:

http://community.qlik.com/message/653660#653660

[martin_dideriks]

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

[rubenmarin · Accepted solution]

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.

[surajap123]

Thanks everyone for the valuable information.

[#7]

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

[rubenmarin]

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.

[#9]

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>

[rubenmarin]

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."