Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to populate referenced columns from a single table.

Hi, I am new to QlikView, so not sure if this question is already addressed. There are 2 tables that is imported in QlikView. Each table has a field "Location".

There is a table "Sales" , "Location Info".

Now the problem is when I try to get values of "Location" corresponding to a single table "Sales" . I get the values of "Location" present in Location table as well.

Can we restrict the "Location" value from a single table, maintaing the relation between table. So that I can see "Location desc" etc, from the other table when I select a "Location" from Sales table.?

1 Solution

Accepted Solutions
kaushik_solanki
Not applicable

Re: How to populate referenced columns from a single table.

Hi,

     Then for this you dont require the Exists function. All you do is load the full data.

     Now when ever you are looking the sales data in any chart, it will show you only the list of sales person which are associated with sales data.

     Only when you put listbox you will see all the sales person id and name.

     Have a look at the attached example for more clarification.

Regards,

Kaushik Solanki

10 Replies
khadeer_sparks
Not applicable

Re: How to populate referenced columns from a single table.

Hi,

Do onr thing i hope ur Sales table is transaction table, common field is location...

Then make left join to both

Sales Table

Left join

Master table...

I hope u will get what i required...

Please check both location carring same field values r not.

Regards,

Khadeer

Not applicable

Re: How to populate referenced columns from a single table.

Hi,

Yes thats an option, but is there any way, to keep it in the distributed form(normalized form), and still achive this.

Can I just explore a single table's field if the field has a associated field in another table?

Thanks and Regards,

Satish

kaushik_solanki
Not applicable

Re: How to populate referenced columns from a single table.

Hi Satish,

     You can try below code.

     Sales:

     Load * from  Sales;

     Location:

     Load * from location where exists(Location,Location);

     This script works as follows.

     Sales will get you all the location data, where as location will get only the location details which are available in Sales table.

Regards,

Kaushik Solanki

Not applicable

Re: How to populate referenced columns from a single table.

Hi Kaushik,

Thats a great trick.

But still the problem is in some other Charts, I need all values from the other table.

Potentailly back to round Zero.

Thanks and Regards,

Satish Pradhan

kaushik_solanki
Not applicable

Re: How to populate referenced columns from a single table.

Hi,

     Could you please give an example for more clarification.

Regards,

Kaushik Solanki

Not applicable

Re: How to populate referenced columns from a single table.

Hi Kaushik,

Suppose I need a graph depecticting the Inventory position accross all Location.

Now as we have loaded only those locations which are available in Sales table, we lost some datas, right.

So now the graph is not accurate.

Anyways, for the previous answer as you suggested, can you please give an exact syntax, for the scripts, or a demo sort of thing. I am very new to Qlikview and not good with scripting. So I was unable to use you idea, as I was unable to create the corresponding script.

Really appreciate your time and analytical ability to tackel questions.

Thanks and Regards,

Satish Pradhan

kaushik_solanki
Not applicable

Re: How to populate referenced columns from a single table.

Hi,

     Have a look at the excel attached.

     Here I have used two tables

     1. Sales :With 20 sales person's Sales value

     2. Sales person :With Sales person's ID and Name

     If you look in the Qvw file attached where i have used the exists function, which is fetching only the names of sales person which are there in Sales table.

     Hope this will clear your idea.

Regards,

Kaushik Solanki

jontydkpi
Not applicable

Re: How to populate referenced columns from a single table.

Satish

The way to do this is to create a qualified location fields along with the original field. Something like this:

Sales:

LOAD ...

     Location,

     Location As Sales.Location,

     ...

From Sales;

Outer Join (Sales)

LOAD

          Location,

          Location As Location.Location,

          ...

From Location;

Now the Location field behaves the same as before, and Sales.Location will only show the fields in Sales and Location.Location will show the fields from Location.

Do not confuse these qualified names, they are NOT the same as qualified fields in SQL, they are simply other field names. Qlikviews expression language has no concept of tables, only of fields.

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: How to populate referenced columns from a single table.

Hi Kaushik,

Thanks for the sample and the super quick response.

Just a quick question. If I need a table, which shows all the Sales-person, then I am losing dat from ID from 21 to 26 .

See now we have a loss of.

Now is there any way, to let the enitire data be loaded, but restrict the values in a graph/table to that which appears in the a single table or file.

Thanks and Regards,

Satish Pradhan