Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

10 Replies
khadeer
Specialist
Specialist

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
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Could you please give an example for more clarification.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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