Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.?
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
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
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
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
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
Hi,
Could you please give an example for more clarification.
Regards,
Kaushik Solanki
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
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
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
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