Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jduenyas
Contributor III

Concatenating Tables

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

4 Replies
Highlighted
Not applicable

Re: Concatenating Tables

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.

jduenyas
Contributor III

Re: Concatenating Tables

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

Not applicable

Re: Concatenating Tables

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

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;