Data model issues

[Leo2]

Hello everyone! I'm struggling with building a data model for historical events and personalities. It looks very simple, but I need your help. So let me try to describe the problem. For example we have 3 fields: Event, Person and Country. The event would be WW2 (the second world war). It's connected with few Persons: Hitler and Stalin. And also the Event connected to some Countries like USSR and Germany. Then the question is: how to avoid of connection of Hitler and USSR? In front end if I select USSR from a Country list box, I don't want to see Hitler in a selection of Persons.

I've already read about data model loops and one of the solutions is to rename fields. In my case for example to make a field like Country_Person and Country_Event. But then I would like to see a combined list box for these 2 fields in front end. Is it possible? Or how would you solve such problem in general?

Thanks in advance.

[anushree1]

Please share a sample input and desired output to understand the issue better.

If both country and person columns occur in the same table the association is bound to occur

[jensmunnichs]

What you could do is create Country_Person and Country_Event, and then load both of those fields into a table completely separate from the data model, and then filtering that table to only contain unique values. Then, create a listbox for the new country table. Next, go to Settings -> Document Properties -> Triggers -> Field Event Triggers, select the field that contains the list of countries, choose 'Add Action(s)...' under 'OnSelect'. Add two 'Select in Field' actions, one with field 'Country_Event', and one with 'Country_Person', and =GetFieldSelections(Country) as Search String.

If you now make a selection in 'Country', it will select that country in both Country_Person and Country_Event. Keep in mind that this will only show lines where Country_Person AND (not or) Country_Event match the selection in Country. However, judging by the first paragraph in your post, it seems like that is what you want.

All that being said.... I would personally just keep the 2 country fields separate. This would make the application you're making a lot more flexible, and I don't think it's a huge deal for people to have to make 2 selections instead of 1. This would also give people the option to run query's such as 'Which people from USSR have been involved in events happening in Germany'?

[Leo2]

Thanks for the detailed reply. I'll keep in mind this solution. Just because such problems is new to me I would love to hear the alternatives if they exist. I also made a simple example for those who would like to have a look (file attached).

The code looks like:

Events:
LOAD * Inline [
Event,Country
WW2,USSR
WW2,Germany
];

Persons:
LOAD * Inline [
Person,Country
Stalin,USSR
Hitler,Germany
Nobel,Sweden
Lenin,USSR
];

So if I choose WW2 in the list box, just Nobel is greyed out in Person list box, but Lenin needs to be excluded as well. That's the thing.

[anushree1]

Try the below code:

Events:
LOAD * Inline [
Event,Country
WW2,USSR
WW2,Germany
];

Persons:
LOAD * Inline [
Person,Country_New
Stalin,USSR
Hitler,Germany
Nobel,Sweden
Lenin,USSR
];

[jensmunnichs · Accepted solution]

How do you know Lenin should be excluded as well? Because you know that Lenin was not connected to WW2, but how would Qlikview know that? All you've told Qlikview is that Lenin is from the USSR, and the USSR is linked to WW2. That's why you're going to need a table that includes information on which people are connected to which events instead of linking the tables using the country field.

One option would be to have the Persons table include fields like Person_ID, name, CountryOfOrigin, etc. and to have the Events table include fields like Event_ID, Event, Country, date, etc. and then create a table in between matching the correct Person_ID's to the correct Event_ID's.

Now that the tables are no longer connected through the Country field, selections should give you the right results, and if you still want to combine the country fields into one listbox, you get back to my previous comment (though I still don't recommend doing that).

[Leo2]

Thank you. Seems like it works this way:

Events:
LOAD * Inline [
EventID,Event,Country
1,WW2,USSR
1,WW2,Germany
2,Revolution,USSR
];

Persons:
LOAD * Inline [
PersonID,Person,CountryOrigin
1,Stalin,USSR
2,Hitler,Germany
3,Nobel,Sweden
4,Lenin,USSR
];

Connector:
LOAD * Inline [
EventID,PersonID
1,1
1,2
2,4
];

Just need to figure out how to connect EventID and PersonID automatically if it's possible. Any ideas?

[jensmunnichs]

Well, as I said before, Qlikview doesn't know anything without us providing it with data, so if you're doing everything with inline tables, I think this is kind of the only option you've got.

However, I'm assuming you're actually working with more data than what you're showing here, and you're loading the data into the application from some sort of source (QVD, Excel, etc.). In that case, it really depends on what your source tables look like. Try googling for 'link tables' for more help with this or, if possible, share (part of) your application and I can try help you out.

[Leo2]

Many thanks for your help. I will try to figure out the best strategy myself now.