topic Merge two database into one in QlikView

Merge two database into one

sakshikaul — Wed, 25 Nov 2020 16:16:04 GMT

Dear All,

I have a following query:-

I was having two database in which table name was same in both the database

so from back-end the data was merged(and IP address was changed in qlikview as well) into one and each tables were having following conditions applied

Now I want to apply both the below conditions(marked in red colour) in one table only(NEW TABLE ONLY). how to do the same ?

Table 1 with old database

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=qlikview;Initial Catalog=BIORAD_QCDAO;Data Source=111.111.1111.36;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=QVNRL-SRV-POC;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is aKeddYRNJbaMXUVMXDQCDZMGTD);(old database connection)
Concatenate($(vTableName))
LOAD *,
Mid(LABNAME,1,index(LABNAME,'-',1)-1) as Lab_Code,
Mid(LABNAME,index(LABNAME,'-',1)+1,index(LABNAME,'-',2)-index(LABNAME,'-',1)-1) as Lab_Location,
Mid(LABNAME, index(LABNAME,'-',2)+1, Len(LABNAME)-index(LABNAME,'-',2)) as Lab_Zone;
SQL SELECT ANALYTE,
CITY,
Comment,
CorrectiveAction,
ExpiredDate,
FixedCV,
FixedMean,
FixedSD,
INSTRUMENT,
LABID,
LABNAME,

LevelName,
LevelValue,
LOT_NAME,
LOT_NO,
Operator,
Status,
TEST_DATE,
WestgardRuleViolation,
ZScore,
Matrix,
REAGENT,
UNIT,
TEMPERATURE
FROM "BIORAD".dbo."DASHBOARD";

STORE $(vTableName) into $(vQvdPathStage1)$(vTableName).qvd(qvd);

DROP Table $(vTableName);

NEW table new connection string

$(vTableName):
LOAD *,
if(LABID = '166051','S03',
if(Match(LABID,'180128','180130','180132','180138','180140','180142','180144','180148','180150','180152','180154','180156','180163'), 'L51','LPL')) as Lab_Code,
if(LABID = '166051','Preet Vihar',
if(Match(LABID,'180128','180130','180132','180138','180140','180142','180144','180148','180150','180152','180154','180156','180163'),'KRL','NRL')) as Lab_Location,
if(LABID = '166051','DNCR',
if(Match(LABID,'180128','180130','180132','180138','180140','180142','180144','180148','180150','180152','180154','180156','180163'),'KRL','NRL')) as Lab_Zone;

SQL SELECT ANALYTE,
CITY,
Comment,
CorrectiveAction,
ExpiredDate,
FixedCV,
FixedMean,
FixedSD,
INSTRUMENT,
LABID,
LABNAME,
LevelName,
LevelValue,
LOT_NAME,
LOT_NO,
Operator,
Status,
TEST_DATE,
WestgardRuleViolation,
ZScore,
Matrix,
REAGENT,
UNIT,
TEMPERATURE

FROM "BIORAD_QCDAO".dbo."DASHBOARD";

Re: Merge two database into one

hallquist_nate — Thu, 18 Jul 2019 16:40:35 GMT

Probably the easiest way to do this is to first concatenate the two tables, with no conditions applied. then reload the newly concatenated table using a resident load, and apply your conditions, then Drop the original table. I uploaded a text file with what the script might look like. My SQL skills are pretty rusty, so you will need to clean that up, but you should get the idea.

Also, you will probably need to qualify the field names. I have run into versions of Qlik where the Drop Table will drop all the fields that are named the same. so if you don't changed the names in your Temp table, to be different from the Field names in your New Table, the Dop Table statement may also drop the fields in your New Table, inadvertently. Just something to be aware of.

Good luck,

Nate

Re: Merge two database into one

Brett_Bleess — Thu, 08 Aug 2019 16:05:04 GMT

Did Nate's post help you resolve things? If so, please be sure to give credit by clicking the Accept as Solution on the post. If you are still struggling, leave an update, and if you used a different solution, it would be great if you could post that for others, so they may learn from it too.

Regards,
Brett