topic Re: qualify and concatenation issue in QlikView

qualify and concatenation issue

Wed, 30 Oct 2013 08:17:11 GMT

Hi guys,

I am concatenating few tables which are loaded from QVD files.

I am using QUALIFY * statement so prefix the column name to the concatenated table name but i see a strange behavior that only first table's column is getting qualified with concatenated table name and rest of the table'column are qualified by original QVD file name.

QUALIFY *;

UNQUALIFY TxnID;

[Sales]:

Load

TxnID as S.TxnID,

Amount

from

invoice.qvd;

Concatenate(Sales)

Load TxnID as S.TxnID,

Amount

from

SalesOrder.qvd;

Here is the list of columns

TxnID (expected S.TxnID)

Sales.Amount

SalesOrder.Amount (not expected)

What am I doing wrong here?

Re: qualify and concatenation issue

Wed, 30 Oct 2013 08:19:41 GMT

You Need to put UNQUALIFY *; After your Load statement, and then Qualify your newb table also:

ex:

QUALIFY *;

UNQUALIFY TxnID;

[Sales]:

Load

TxnID as S.TxnID,

Amount

from

invoice.qvd;

UNQUALIFY *;

Concatenate(Sales)

QUALIFY *;

UNQUALIFY S.TxnID

TABLE1:

Load TxnID as S.TxnID,

Amount

from

SalesOrder.qvd;

UNQUALIFY *;

Furthermore I will suggest you change your names so they dont contain a "." - Otherwise put them in brackets []

Re: qualify and concatenation issue

Wed, 30 Oct 2013 08:26:18 GMT

you meant like this

QUALIFY *;

UNQUALIFY TxnID;

[Sales]:

Load

TxnID as S.TxnID,

Amount

from

invoice.qvd;

UNQUALIFY *;

QUALIFY *;

UNQUALIFY TxnID;

Concatenate(Sales)

Load TxnID as S.TxnID,

Amount

from

SalesOrder.qvd;

Re: qualify and concatenation issue

Wed, 30 Oct 2013 08:27:35 GMT

Yes ! - Was just about to write it

Re: qualify and concatenation issue

tresB — Wed, 30 Oct 2013 08:28:52 GMT

Try like:

[Sales_T]:

Load

TxnID as S_TxnID,

Amount

from

invoice.qvd;

Concatenate(Sales)

Load TxnID as S_TxnID,

Amount

from

SalesOrder.qvd;

Qualify *;

Unqualify S_TxnID;

Sales:

Load * resident Sales_T;

Drop Table Sales_T;

Re: qualify and concatenation issue

Wed, 30 Oct 2013 08:29:34 GMT

A good best pratice is to give your tables names (So they dont take their name from the source) - Then it is easier to handle imo.

Re: qualify and concatenation issue

Wed, 30 Oct 2013 08:43:25 GMT

Can you please give me a sample code because it is not working even after i changed the code according to your suggestion?

Re: qualify and concatenation issue

Wed, 30 Oct 2013 08:54:06 GMT

but i need to concatenate these two tables so how can i give separate tables names?

Re: qualify and concatenation issue

Wed, 30 Oct 2013 08:56:04 GMT

Concatenate(Sales)

gives error because there is no tables with this name.

Re: qualify and concatenation issue

Wed, 30 Oct 2013 09:08:37 GMT

this works for me:

QUALIFY *;

UNQUALIFY ost;

table1:

LOAD ProductKey,

SalesOrderNumber as ost,

SalesOrderLineNumber;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.FactInternetSales;

QUALIFY*;

UNQUALIFY ost;

Concatenate(table1)

table2:

LOAD SalesOrderNumber as ost,

SalesReasonKey;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.FactInternetSalesReason;

UNQUALIFY*;

//-------- End Multiple Select Statements ------

Re: qualify and concatenation issue

Wed, 30 Oct 2013 09:14:56 GMT

This code will give me fields like

Sales.Amount

Table1.Amount

right?

if then it will not work for me becasue I need to have only one field

i.e. Sales.Amount

Re: qualify and concatenation issue

tresB — Wed, 30 Oct 2013 09:16:02 GMT

Rightly pointed out.

Try:

Concatenate(Sales_T)

or, simple remove that line. Having all fields similar it will get auto concatenated.

Re: qualify and concatenation issue

Wed, 30 Oct 2013 09:23:14 GMT

Thanks, It works.

Re: qualify and concatenation issue

Wed, 30 Oct 2013 09:23:58 GMT

Rename your Sales Amount to Amount and qualify it. Then there will only be one