Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
pranaview
Contributor II

How can we create a One to Many relation using Mapping

Hi Guys,

I'm in dire need of help here. So, I am building an app. I have a base data sheet (Let's say OrderSheet)which has Order numbers(unique) among other dozens of fields and I have another file(Let's say MappingSheet)which is required to get the Field1 and Field2 info for the order numbers that are available in the base file. The MappingSheet has Order numbers but each Order number is available multiple times depending on how many time Field1 and Field2 values have been updated. So for ex : Order number : 8876324823 has a single record in OrderSheet but multiple records in MappingSheet with different values in Field1 and Field2 logged at different time.

The initial requirement was to map the Order number from OrderSheet in the MappingSheet and simply get the latest record which was easy using ApplyMap().But now client is asking that they want the whole info. so for ex : If Order number 8876324823 has 10 records in MappingSheet then I have to take all the 10 ones and show it in the dashboard not just the latest record. 

I thought maybe I'll just load the MappingSheet as a new table and associate it with the OrderSheet table using the Order number field but I am not able to get the desired result in the dashboard. I am very new to QV so maybe i'm missing something very basic here.

I really need to come up with a solution and quickly so any help will be appreciated.

Thanks,

Pranav

1 Solution

Accepted Solutions
Partner
Partner

Re: How can we create a One to Many relation using Mapping

You could load with a LEFT KEEP. 

LEFT KEEP (OrderSheet) LOAD Fields FROM MappingSheet;

In that case only the order numbers existing in the table: OrderSheet are loaded from the MappingSheet. 

6 Replies
Partner
Partner

Re: How can we create a One to Many relation using Mapping

HI @pranaview

 


@pranaview wrote:

 

I thought maybe I'll just load the MappingSheet as a new table and associate it with the OrderSheet table using the Order number field but I am not able to get the desired result in the dashboard. I am very new to QV so maybe i'm missing something very basic here.


That is the right way to do it. Are you getting syntetic keys ,or loop error messages? The tables in the datamodel should be linked with one field. For example use; OrderNumber AS %OrderNumber_key in both tables. Then Qlik automatically associates the data. 

pranaview
Contributor II

Re: How can we create a One to Many relation using Mapping

Hi,
Thanks for the quick reply.
I did what you have suggested and there is no syn key or loop error as well. I think where i'm struggling is how to update my chart expressions.
For ex : I am showing Total count of order numbers i.e. Count.(DISTINCT Order_Number) Now it has obviously changed since I have associated both tables with Order number field. Similarly in other charts also i am facing same kind of issues. Struggling to get the expressions right. Like for another ex: I have a Block chart where i have to show the distribution Orders with respect to the Field1 but if i do that, it will do the count on all Field1 records present in the MappingSheet not just the ones whose Order Number is available in OrderSheet.

Hope you got an idea where I am struggling.
Partner
Partner

Re: How can we create a One to Many relation using Mapping

Hi @pranaview,

You could try; Count.(Order_Number), because Count.(DISTINCT Order_Number) counts only the unique numbers. 

Besides that counting on key fields (you can see them in datamodel viewer) can result in wrong quantities .Rename the fields that are linked for example as I mentioned in the previous post. Then only use Order_Number in the order header table and count the order lines from another field.

pranaview
Contributor II

Re: How can we create a One to Many relation using Mapping

Hi,
So what your saying is that associate tables using %OrderNumber_key and then for counting records, use a different field?
But the problem is for ex: I have to use Field1 (from MappingSheet table) for a Block Chart and Field2 for a Bar chart and count all the records for which Order Number is present in both the tables. How do I do that?
Partner
Partner

Re: How can we create a One to Many relation using Mapping

You could load with a LEFT KEEP. 

LEFT KEEP (OrderSheet) LOAD Fields FROM MappingSheet;

In that case only the order numbers existing in the table: OrderSheet are loaded from the MappingSheet. 

pranaview
Contributor II

Re: How can we create a One to Many relation using Mapping

Hi @avkeep01

Left Keep approach solved my problem. Thanks for the suggestion. Will close the thread now.

Pranav