Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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 []
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;
Yes ! - Was just about to write it
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;
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.
Can you please give me a sample code because it is not working even after i changed the code according to your suggestion?
but i need to concatenate these two tables so how can i give separate tables names?
Concatenate(Sales)
gives error because there is no tables with this name.
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 ------