two QVD join using multiple fields and interval match

[#1]

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.

[bgerchikov]

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.