Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

asmithbi
Contributor II

Need to Add Identifier to Concatenate Data Load

Hello,

I am merging multiple SQL tables that are the exact same using the Concatenate statement in the load script below.  Each ODBC connection represents a separate retail store database that uses the number of the retail store as the database name (i.e. 022, 071, 073, etc...).  The issue is the SQL payment table in the load script does not have a field for the retail store number.  I need to be able to filter by each retail store in the application. 

How can I insert a field that populates the respective retail store number for each table loaded?

Thank you in advance for any assistance with this issue. 

ODBC CONNECT TO [MVE-022] (XUserId is JUVbfZFMQB, XPassword is AXXKUGZOFDOOTbAHYRMODaUGWJSB);
SQL SELECT * FROM "022".dbo.payments;

ODBC CONNECT TO [MVE-071] (XUserId is YXYGeZFMRA, XPassword is GKNOWGZOFDOOTbAHYRMODaUGWBKA);
Concatenate  SQL SELECT * FROM "071".dbo.payments;

ODBC CONNECT TO [MVE-073] (XUserId is IYAHaZFMCA, XPassword is YQPTaGZOFDOOTbAHYRMODaUGWJRB);
Concatenate SQL SELECT * FROM "073".dbo.payments;

ODBC CONNECT TO [MVE-118] (XUserId is dZBFdZFMKD, XPassword is RMaXZGZOFDOOTbAHYRMODaUGWZWA);
Concatenate SQL SELECT * FROM "118".dbo.payments;

ODBC CONNECT TO [MVE-119] (XUserId is OKCLRZFMAG, XPassword is SPAXaGZOFDOOTbAHYRMODaUGWBZA);
Concatenate SQL SELECT * FROM "119".dbo.payments;

ODBC CONNECT TO [MVE-169] (XUserId is DaPWXZFMRG, XPassword is KSKCUGZOFDOOTbAHYRMODaUGWZeB);
Concatenate SQL SELECT * FROM "169".dbo.payments;

ODBC CONNECT TO [MVE-170] (XUserId is DWHIcZFMZD, XPassword is DTEOXGZOFDOOTbAHYRMODaUGWRMA);
Concatenate SQL SELECT * FROM "170".dbo.payments;

ODBC CONNECT TO [MVE-233] (XUserId is JLKVcZFMQF, XPassword is VHHSaGZOFDOOTbAHYRMODaUGWBAA);
Concatenate SQL SELECT * FROM "233".dbo.payments;

ODBC CONNECT TO [MVE-335] (XUserId is KdKZUZFMVD, XPassword is eeMAVGZOFDOOTbAHYRMODaUGWZJB);
Concatenate SQL SELECT * FROM "335".dbo.payments;

ODBC CONNECT TO [MVE-418] (XUserId is dFPYeZFMZF, XPassword is WZbZSGZOFDOOTbAHYRMODaUGWZFB);
Concatenate SQL SELECT * FROM "418".dbo.payments;

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Need to Add Identifier to Concatenate Data Load

if all your table have the same number and name of fields, remove concatenate and you should get one table

if they have different fields name and/or numbers, force a concatenate

ODBC CONNECT TO [MVE-22] (XUserId is JUVbfZFMQB, XPasswordisAXXKUGZOFDOOTbAHYRMODaUGWJSB);


table:

LOAD *, '022' as NewField;

SQL SELECT * FROM "022".dbo.payments;

ODBC CONNECT TO [MVE-71] (XUserId is YXYGeZFMRA, XPasswordisGKNOWGZOFDOOTbAHYRMODaUGWBKA);


concatenate (table)

LOAD *, '071' as NewField;

SQL SELECT * FROM "071".dbo.payments;

ODBC CONNECT TO [MVE-73] (XUserId is IYAHaZFMCA, XPasswordisYQPTaGZOFDOOTbAHYRMODaUGWJRB);


concatenate (table)

LOAD *, '073' as NewField;

SQL SELECT * FROM "073".dbo.payments;


6 Replies

Re: Need to Add Identifier to Concatenate Data Load

Use a Preceding load on each concatenate.

asmithbi
Contributor II

Re: Need to Add Identifier to Concatenate Data Load

Bill,

Not sure of that syntax.  Can you provide? 

Thank you,

MVP
MVP

Re: Need to Add Identifier to Concatenate Data Load

you can add a field in sql

SQL SELECT '022' as NewField, p.* FROM "022".dbo.payments p;


or add a field in the load

load *, '022' as NewField;

SQL SELECT * FROM "022".dbo.payments;

asmithbi
Contributor II

Re: Need to Add Identifier to Concatenate Data Load

Hi Massimo,

I added the preceding script to the load script.  Is the syntax correct?  Thank you,

ODBC CONNECT TO [MVE-022] (XUserId is JUVbfZFMQB, XPassword isAXXKUGZOFDOOTbAHYRMODaUGWJSB);

LOAD *, '022' as NewField;

SQL SELECT * FROM "022".dbo.payments;

ODBC CONNECT TO [MVE-071] (XUserId is YXYGeZFMRA, XPassword isGKNOWGZOFDOOTbAHYRMODaUGWBKA);

LOAD *, '071' as NewField;

Concatenate  SQL SELECT * FROM "071".dbo.payments;

ODBC CONNECT TO [MVE-073] (XUserId is IYAHaZFMCA, XPassword isYQPTaGZOFDOOTbAHYRMODaUGWJRB);

LOAD *, '073' as NewField;

Concatenate SQL SELECT * FROM "073".dbo.payments;

MVP
MVP

Re: Need to Add Identifier to Concatenate Data Load

if all your table have the same number and name of fields, remove concatenate and you should get one table

if they have different fields name and/or numbers, force a concatenate

ODBC CONNECT TO [MVE-22] (XUserId is JUVbfZFMQB, XPasswordisAXXKUGZOFDOOTbAHYRMODaUGWJSB);


table:

LOAD *, '022' as NewField;

SQL SELECT * FROM "022".dbo.payments;

ODBC CONNECT TO [MVE-71] (XUserId is YXYGeZFMRA, XPasswordisGKNOWGZOFDOOTbAHYRMODaUGWBKA);


concatenate (table)

LOAD *, '071' as NewField;

SQL SELECT * FROM "071".dbo.payments;

ODBC CONNECT TO [MVE-73] (XUserId is IYAHaZFMCA, XPasswordisYQPTaGZOFDOOTbAHYRMODaUGWJRB);


concatenate (table)

LOAD *, '073' as NewField;

SQL SELECT * FROM "073".dbo.payments;


asmithbi
Contributor II

Re: Need to Add Identifier to Concatenate Data Load

Thank you!!

Community Browser