Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ishpat2016
New Contributor III

Associate 2 non linked Fields

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.

Tags (1)
1 Solution

Accepted Solutions
YoussefBelloum
Esteemed Contributor

Re: Associate 2 non linked Fields

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

.

View solution in original post

12 Replies
Partner
Partner

Re: Associate 2 non linked Fields

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
Esteemed Contributor

Re: Associate 2 non linked Fields

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

.

View solution in original post

MVP
MVP

Re: Associate 2 non linked Fields

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
YoussefBelloum
Esteemed Contributor

Re: Associate 2 non linked Fields

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
Contributor II

Re: Associate 2 non linked Fields

Can you use Pick and Match function to hardcore it

But this is not suggestable

Better to create a link table

YoussefBelloum
Esteemed Contributor

Re: Associate 2 non linked Fields

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
Contributor II

Re: Associate 2 non linked Fields

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
Esteemed Contributor

Re: Associate 2 non linked Fields

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 ?

Partner
Partner

Re: Associate 2 non linked Fields

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.