Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
I am trying to get some stats of our Project Engineers (PE) and am running into some difficulties while concatenating tbales.
Simplified -- I have a table for Quotes (tblQuotes), table for Orders (tblOrders) and table for Sales (tblSales)
In the script I am trying to concatenate Quotes and Orders into a table which I call PE_STATS. Since I have many other tables in the script I am qualifying the table with a Qualify * statement. Here is the simplified script:
Qualify *;
Unqualify TranDate; ' (this will link to my Calendar Table)
PE_STATS: 'Name of the derived table
Load
Source
, ProjectEngineerName AS PE
, QuoteTotal
, QuoteDate AS TranDate
, CustomerName
, QuoteID;
SQL SELECT 'Quotes' AS Source, ProjectEngineerName, QuoteTotal, QuoteDate, CustomerName, QuoteID
FROM tblQuotes;
Concatenate Load
Source
, ProjectEngineerName AS PE
, OrderTotal
, OrderDate AS TranDate
. OrderID;
SQL SELECT 'Orders' AS Source, tblOrders.ProjectEngineerName, tblSales.OrderTotal, tblOrders.OrderDate, tblOrders.OrderID
FROM tblOrders INNER JOIN
tblSales on tblSales.OrderID = tblOrders.OrderID;
I was expecting the fields to be qualified as
TranDate (This is not qualified)
PE_STATS.Source
PE_STATS.PE
PE_STATS.QuoteTotal
etc.
but instead the fields are qualified like so:
tblQuotes.Source
tblQuotes.ProjectEngineerName
tblQuotes.QuoteDate
....
tblOrders.Source
tblOrders.OrderTotal
etc.
The field are being qualified by the FIRST table's name from which they were queried!
(If I change the order of the tables in the second query and set tblSales to be first in the list of tables, the fields will take the name tblSales.Source... etc)
Is that normal or am I doing something wrong? How can I combine the 2 tables into one in the script?
(I know that I can run a union query in the SQL Server but that becomes more cumbersome.)
Thanks
Josh
Hi,
Yes it is a normal qlikview behaviour using "Qualyfy" to get something as you spect try this:
// look I don't use "Qualify" in this moment
PE_STATS: 'Name of the derived table
Load
Source
, ProjectEngineerName AS PE
, QuoteTotal
, QuoteDate AS TranDate
, CustomerName
, QuoteID;
SQL SELECT 'Quotes' AS Source, ProjectEngineerName, QuoteTotal, QuoteDate, CustomerName, QuoteID
FROM tblQuotes;
Concatenate(PE_STATS)
Load
Source
, ProjectEngineerName AS PE
, OrderTotal
, OrderDate AS TranDate
. OrderID;
SQL SELECT 'Orders' AS Source, tblOrders.ProjectEngineerName, tblSales.OrderTotal, tblOrders.OrderDate, tblOrders.OrderID
FROM tblOrders INNER JOIN
tblSales on tblSales.OrderID = tblOrders.OrderID;
// you will get a Table PE_STATS whit This Structure:
Source, // From tblQuotes and tblSales + tblOrders
PE, // From tblQuotes and tblSales + tblOrders
QuoteTotal,
TranDate, // From tblQuotes and tblSales + tblOrders
CustomerName,
QuoteID,
OrderTotal,
OrderID
Then if you need Qualify this table try this:
Qualify *;
UnQualify TranDate;
No Concatenate
PE_STATS_:
Load
*
Resident PE_STATS;
UnQualify *; // If you need more table after load this.
Drop Table PE_STATS;
// Solution of your request
But in my opinion is better do it like more simple, something like this:
// This is the option B
PE_STATS: 'Name of the derived table
Load
Source
, ProjectEngineerName AS PE
, QuoteTotal AS QuoteTotal_ // rename to distinct from others field with the same name
, QuoteDate AS TranDate
, CustomerName AS CustomerName_ // rename to distinct from others field with the same name
, QuoteID AS QuoteID_; // rename to distinct from others field with the same name
SQL SELECT 'Quotes' AS Source, ProjectEngineerName, QuoteTotal, QuoteDate, CustomerName, QuoteID
FROM tblQuotes;
Concatenate(PE_STATS)
Load
Source
, ProjectEngineerName AS PE
, OrderTotal AS OrderTotal_ // rename to distinct from others field with the same name
, OrderDate AS TranDate
. OrderID AS OrderID_ ; // rename to distinct from others field with the same name
SQL SELECT 'Orders' AS Source, tblOrders.ProjectEngineerName, tblSales.OrderTotal, tblOrders.OrderDate, tblOrders.OrderID
FROM tblOrders INNER JOIN
tblSales on tblSales.OrderID = tblOrders.OrderID;
// And that's all, you don't need Qualify to get your solution.
Best regards.
Thank you Oswaldo.
Unfortunately I do need to qualify the table because some of the fields are already in use and exist in my data structure.
When we just started using QV, the application was small and limited and we did not bother to qualify any of the tables/fields. but as it grew much larger and many more tables were added we had to start qualifying the fields.
I think I have a solution and if it works I will post it here so it is shared with all.
Thanks again
Hi,
First load the data from both the tables , store it in a qvd. Concatenate both the qvd's. post concatenation take a resident in which you may apply qualify and unqualify.
Regards
Yashwin
Both Concatenate and Qualify won't work together well as expected. You have to do it in a sequence
1.Do Concatenate
PE_STATS_TEMP:
Load
...;
SQL SELECT * FROM tblQuotes;
CONCATENATE
Load
...;
SQL SELECT * FROM tblOrders;
2. Qualify and load the table using resident
Qualify *;
Unqualify TranDate; ' (this will link to my Calendar Table)
PE_STATS:
Load
*
Resident PE_STATS_TEMP;
3. Drop the temporary table
Drop Table PE_STATS_TEMP;