two QVD join using multiple fields and interval match

Sun, 19 Aug 2012 06:56:28 GMT

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.

Re: two QVD join using multiple fields and interval match

bgerchikov — Mon, 20 Aug 2012 03:23:45 GMT

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.

topic two QVD join using multiple fields and interval match in QlikView

two QVD join using multiple fields and interval match

Re: two QVD join using multiple fields and interval match