Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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";
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
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