map multiple fields to one field (fieldname)

[#1]

I want to map multiple fields to one field (fieldname). I am adding 4 sheets to a dashboard with 10 sheets. Each of the first ten sheets uses a list box for Year with the field DXYEAR as the source of the data. Two of the sheets I am adding also have a Year field MXYEAR and two have a field GXYEAR . I would like to map the fields MXYEAR and GXYEAR to DXYEAR so I can use the same Listbox for selection and when the value in the listbox changes on one sheet all 14 sheets will reflect the change. I tried to just rename the MXYEAR and GXYEAR fields as they were loaded but it caused too many Symthetic keys to be created.

[johnw · Accepted solution]

If it formed synthetic keys, your data model probably doesn't lend itself to working this way.

Let me give you an example that will perhaps explain what is going wrong. A customer places an order, so I have an order date. I produce the item, so I have a production date. I ship the item, so I have a ship date.

Now I have three tabs, an order tab, a production tab, and a shipping tab. I put a Year list box on each tab. When I'm on the order tab, I want the date to refer to the date it was ordered. When I'm on the production tab, I want the date to refer to the date it was produced. When I'm on the shipping tab, I want the date to refer to the date it was shipped.

So I do what you did - I just make a single date field. I go to the order tab, select January 15, 2009, and my order chart is suddenly empty. Wait, I KNOW I had orders on January 15, 2009. What happened???

What happened is that you made all the date fields the same. So when you selected January 15, 2009, you were telling the system that you only wanted to see data where the order date was January 15, 2009, AND the production date was January 15, 2009 AND the ship date was January 15, 2009. So unless material was ordered, produced and shipped on the same day, you won't see a thing.

There are ways around that, of course. A common one is to create a "date island". In your case, you would still have your three separate fields, DXYEAR, MXYEAR and GXYEAR. Then you would make a fourth field, Year, disconnected from all of your data. All list boxes would be for the new Year field. Now what happens when you select 2009? Absolutely nothing, because it isn't connected to anything.

So then you "connect" it manually using an expression in the chart. If, for instance, you want the sum of sales for the DXYEAR, you write the expression sum(if(DXYEAR=Year,Sales)). QlikView will look through ALL sales, but only add them to the total when DXYEAR is the Year you selected. The down side of this approach is performance, which can sometimes be bad since you're forcing QlikView to scan through ALL sales, not just sales for the year. But it may be sufficient for your purposes. If that doesn't work out, I'm sure there are other approaches that will, but that's where I'd start.

[#3]

Thanks. That would work for year but I am also going to need to handle 12 months and multiple years .I should have been more detailed in my question.

[johnw]

I believe the answer is the same. The expression sum(if(DXYEAR=Year,Sales)) will work fine even when there are multiple values selected for Year, and multiple DXYEARs available. If you want to handle DXQUARTER, DXMONTH, DXWEEK and DXDATE, just do your match at the lowest level of detail, so sum(if(DXDATE=Date,Sales)). If you select, say, two months from your calendar, those months are connected to that calendar's Dates, so the expression will still return the Sales for those two months, even though it doesn't explicitly mention the month fields.