Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have two QVDs with 100 million and 10 milllion rows respectively. They have following fields.
QVD1:
Plan_No
Status
BIN
PCN
GRP
Drug_Id
Fill_Date
.......
QVD2:
C_Plan_No
C_Status
C_BIN
C_PCN
C_GRP
C_Drug_Id
Fill_Date_Start
Fill_Date_End
.......
I want to replace Plan_No, Status and few other attributes from QVD1 with C_Plan_No, C_Status and other attributes from QVD2
where
BIN = C_BIN
and PCN = C_PCN
and GRP = C_GRP
and Drug_Id = C_Drug_Id
and Fill_Date is between (Fill_Date_Start and Fill_Date_End)
What is the most efficient way of doing this in QV 11? Is it apply map? Left Join? Interval match? and how would I Do it? Please use the above example and write some sample load statement with the join syntaxes. Thanks.
Nims,
In assumption that combination of BIN, PCN, GRP, Drug_Id and Fill_Date fields is unique, it might work the following way:
Directory;
QVD1:
LOAD Plan_No,
Status,
BIN,
PCN,
GRP,
Drug_Id,
Fill_Date
FROM
QVD1.xlsx
(ooxml, embedded labels, table is QVD1);
QVD2:
LOAD C_Plan_No,
C_Status,
C_BIN AS BIN,
C_PCN AS PCN,
C_GRP AS GRP,
C_Drug_Id AS Drug_Id,
Fill_Date_Start,
Fill_Date_End
FROM
QVD1.xlsx
(ooxml, embedded labels, table is QVD2);
Left join LOAD
BIN,
PCN,
GRP,
Drug_Id,
Fill_Date
Resident QVD1;
QVD2Temp:
NoConcatenate LOAD
BIN&'-'&PCN&'-'&GRP&'-'&Drug_Id&'-'&Fill_Date as QVD2Link,
C_Plan_No as Plan_No,
C_Status as Status,
BIN,
PCN,
GRP,
Drug_Id,
Fill_Date,
Fill_Date_Start,
Fill_Date_End
Resident QVD2
where Fill_Date>= Fill_Date_Start and Fill_Date<=Fill_Date_End
;
DROP table QVD2;
QVD1Temp:
NoConcatenate LOAD * Resident QVD1
Where not Exists (QVD2Link, BIN&'-'&PCN&'-'&GRP&'-'&Drug_Id&'-'&Fill_Date);
DROP table QVD1;
Concatenate (QVD1Temp)
LOAD
Plan_No,
Status,
BIN,
PCN,
GRP,
Drug_Id,
Fill_Date
Resident QVD2Temp;
DROP table QVD2Temp;
RENAME Table QVD1Temp to QVD1;
Hope it will help.