Associate 2 non linked Fields

[Anonymous]

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 :

A:

LOAD * INLINE [

    State

    MH

    RJ

    MP

];

B:

LOAD * INLINE [

    City

    Pune

    Mumbai

    Jaipur

    Udaipur

    Bhopal

    Indore

    Satara

];

A:

LOAD * INLINE [

    State

    MH

    RJ

    MP

];

B:

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.

[bramkn]

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.

C:

LOAD * INLINE [

    State, City

    MH, Pune

     MH, Mumbai

....

];

[YoussefBelloum · Accepted solution]

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

.

[jonathandienst]

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.

[YoussefBelloum]

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

[qlik4asif]

Can you use Pick and Match function to hardcore it

But this is not suggestable

Better to create a link table

[YoussefBelloum]

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?

[qlik4asif]

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

[YoussefBelloum]

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 ?

[bramkn]

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.