topic Re: Associate 2 non linked Fields in QlikView

Associate 2 non linked Fields

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am in learning phase of QV. Please help me in solving the below issue.

I have 2 List boxes coming from 2 different table and those 2 table have no association with each other. Since, I cannot give the details from project, here is the sample data :

LOAD * INLINE [

State

];

LOAD * INLINE [

City

Pune

Mumbai

Jaipur

Udaipur

Bhopal

Indore

Satara

];

LOAD * INLINE [

State

];

LOAD * INLINE [

City

Pune

Mumbai

Jaipur

Udaipur

Bhopal

Indore

Satara

];

These 2 List Boxes on UI has no link between each other. How do I associate them together so that when I click

1. State: 'MH', City:'Pune,Mumbai' get selected

2. State: 'RJ', City: 'Jaipur, Udaipur'

3. State 'MP': City: 'Bhopal, Indore'

Please help.

Re: Associate 2 non linked Fields

bramkn — Mon, 05 Mar 2018 09:48:31 GMT

Qlik does not know which state has which city linked to it so you will have to create a link table to let Qlik know what to do.

LOAD * INLINE [

State, City

MH, Pune

MH, Mumbai

....

];

Re: Associate 2 non linked Fields

YoussefBelloum — Mon, 05 Mar 2018 09:54:59 GMT

Hi,

you cannot link 2 tables if you don't have a joining key between them, it does not make sense..

you should structure your table(s) before trying to do visualisation with it..

on the script you can create a new field on the STATE or CITY table to add the missing information, like this:

CITY:

LOAD

.if(Match(city,'Pune','Mumbai'), 'MH') as State,

FROM city_table;

like this you will have one table with STATE and CITY infos.

hope it is clear

Re: Associate 2 non linked Fields

jonathandienst — Mon, 05 Mar 2018 10:01:13 GMT

The correct solution is the first proposed by Bram. While it is possible to have hard-coded expressions as per the second suggestion, it is neither efficient nor practical.

Re: Associate 2 non linked Fields

YoussefBelloum — Mon, 05 Mar 2018 10:07:50 GMT

Hi jontydkpi,

the Idea was just joining the fields to have one table instead of one more logical linking table.

What is not practical and efficient in joining like this ?

Thanks

Re: Associate 2 non linked Fields

qlik4asif — Mon, 05 Mar 2018 10:38:23 GMT

Can you use Pick and Match function to hardcore it

But this is not suggestable

Better to create a link table

Re: Associate 2 non linked Fields

YoussefBelloum — Mon, 05 Mar 2018 10:44:30 GMT

I'm sure the remark of Jonathan wan't about what to choose between Pick & match or IF and Match.

I tried to be as much clear and simple as possible especially that the guy said that he is in a learning phase.

my question is why it is not suggestable or efficient or practical?

Re: Associate 2 non linked Fields

qlik4asif — Mon, 05 Mar 2018 11:02:00 GMT

It is nothing but hardcording values....

In future if the new data comes, then again we need to change the expressions, which is not best practice.

My personal opinion is always try to write expression with automation but not with hard coded value(For time being some time giving hard coded value is correct).

Re: Associate 2 non linked Fields

YoussefBelloum — Mon, 05 Mar 2018 11:14:44 GMT

You are right, but here is the definition of HARD-CODE:

embedding an input or configuration data directly into the source code of a program - WIKIPEDIA

when you are inserting data into the script using the INLINE commande, you are hard coding values.

so both options (the first one and mine) are hard coding.

for me the only difference between these two suggestions is the performance and the maintenance of the code, that's why I was asking (because performance is always an interesting topic)

I'm I wrong ?

Re: Associate 2 non linked Fields

bramkn — Mon, 05 Mar 2018 13:48:16 GMT

The inline table option also gives the option to use an Excel/csv table. And is a lot easier to modify than using if/match and that kind of coding.

Why make it hard if you can do it the easy way.

Also using those expressions is hard on the performance because it will need to do those checks every reload. With a link table no checks need to be made. so yes this would be better for the performance.

Re: Associate 2 non linked Fields

YoussefBelloum — Mon, 05 Mar 2018 13:57:05 GMT

I'm ok if it is an external table.

But for this example (a geographic dimension table), I would go for joining all the fields in one table.

Re: Associate 2 non linked Fields

bramkn — Mon, 05 Mar 2018 14:04:20 GMT

We don't know about the data really. He did say this is sample data.

Re: Associate 2 non linked Fields

jonathandienst — Tue, 06 Mar 2018 07:57:51 GMT

Coding the relationships in an expression is simple if you have two or three to consider. But what if you have several thousand? And how would you manage changes to the relationship that is encoded in a very long expression. Finally, any changes or additions require changes to the model, whereas the data driven approach does not.