topic Re: Need to Add Identifier to Concatenate Data Load in QlikView

Need to Add Identifier to Concatenate Data Load

asmithids — Mon, 17 Nov 2014 21:48:43 GMT

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;

Re: Need to Add Identifier to Concatenate Data Load

Anonymous — Mon, 17 Nov 2014 21:51:00 GMT

Use a Preceding load on each concatenate.

Re: Need to Add Identifier to Concatenate Data Load

asmithids — Mon, 17 Nov 2014 22:07:24 GMT

Bill,

Not sure of that syntax. Can you provide?

Thank you,

Re: Need to Add Identifier to Concatenate Data Load

maxgro — Mon, 17 Nov 2014 22:13:46 GMT

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;

Re: Need to Add Identifier to Concatenate Data Load

asmithids — Mon, 17 Nov 2014 22:57:09 GMT

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;

Re: Need to Add Identifier to Concatenate Data Load

maxgro — Mon, 17 Nov 2014 23:35:19 GMT

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;

Re: Need to Add Identifier to Concatenate Data Load

asmithids — Tue, 18 Nov 2014 00:00:01 GMT

Thank you!!