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

How to populate referenced columns from a single table.

Thu, 21 Mar 2013 06:53:29 GMT

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

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

khadeer — Thu, 21 Mar 2013 07:06:05 GMT

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

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

Thu, 21 Mar 2013 08:41:19 GMT

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

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

kaushiknsolanki — Thu, 21 Mar 2013 09:31:05 GMT

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

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

Thu, 21 Mar 2013 14:17:40 GMT

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

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

kaushiknsolanki — Fri, 22 Mar 2013 06:31:40 GMT

Hi,

Could you please give an example for more clarification.

Regards,

Kaushik Solanki

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

Fri, 22 Mar 2013 08:44:46 GMT

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

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

kaushiknsolanki — Fri, 22 Mar 2013 08:59:24 GMT

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

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

jonathandienst — Fri, 22 Mar 2013 09:26:22 GMT

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

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

Fri, 22 Mar 2013 09:29:47 GMT

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

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

kaushiknsolanki — Fri, 22 Mar 2013 09:59:38 GMT

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