topic Re: Concatenating Tables in QlikView

Concatenating Tables

jduenyas — Thu, 21 Aug 2014 20:34:53 GMT

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

Re: Concatenating Tables

Thu, 21 Aug 2014 22:32:54 GMT

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.

Re: Concatenating Tables

jduenyas — Thu, 21 Aug 2014 23:04:45 GMT

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

Re: Concatenating Tables

Fri, 22 Aug 2014 06:24:44 GMT

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

Re: Concatenating Tables

CELAMBARASAN — Fri, 22 Aug 2014 06:32:58 GMT

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;