Merging two tables

deepakqlikview_123 — Wed, 25 Jun 2014 09:55:50 GMT

Hi all,

I have two tables as per below,

EnquiryMaster:

load

'' as EM

resident EnquiryMaster1;

left join(EnquiryMaster)

LOAD

DealerID as EmpDealerID,

EmployeeID as EnquiryExecutorEmployeeID,

EmployeeCode,

FirstName,

MiddleName,

LastName,

(capitalize(FirstName)&' '&capitalize(MiddleName)&' '&capitalize(LastName)) as [Executor Name],

BirthDate,

Sex,

MaritalStatus,

Nationality,

// Address,

// City,

// DistrictID,

// StateID,

Pin,

Phone,

Mobile,

EmailAddress,

Qualification,

Experience,

JoiningDate,

ProbationDuration,

IsInsured,

GradeCode,

Salary,

PVPLTraining,

BloodGroup,

// OutletID,

LeavingDate,

DesignationId,

RoleID,

ReportingTo,

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData,

IsExitInterviewDone,

UserTypeID

FROM

$(QvdPath)\PRE_EmployeeMaster.qvd

(qvd);

join(EnquiryMaster)

load

ReasonID&'-'&SubReasonID&'-'&SubReasonTypeID as ReasonKey,

ReasonID,

capitalize(Reason) as Reason,

SubReasonID,

capitalize(SubReasonName) as SubReasonName,

SubReasonTypeID,

capitalize(SubReasonTypeName) as SubReasonTypeName,

'' as jgk

resident SubReasonTypeMaster;

DROP Table SubReasonTypeMaster;

//left join(EnquiryMaster)

//load

//*,

//' ' as FR

//resident FollowupRegister;

drop Table EnquiryMaster1;//,FollowupRegister;

EnquiryStatus:

LOAD

EnquiryStatusID,

capitalize(EnquiryStatus) as [Enquiry Status]

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData

FROM

$(QvdPath)\PRE_EnquiryStatus.qvd

(qvd);

Dealer1:

LOAD DealerID,

DealerToDealerInvoiceID as DealerInvoiceID,

DealerToDealerInvoiceCode as DealerInvoiceCode,

OutletID,

SerialNumber,

VehicleID,

// SubModelTypeID,

// SubModelID,

date(DealerToDealerInvoiceDate,'DD-MM-YYYY') as DealerInvoiceDate,

ChassisNumber,

// EngineNumber,

//year(date(DealerToDealerInvoiceDate,'DD-MM-YYYY')) as Retail_Year,

year(if(isnull(date(UpdatedOn)) or date(UpdatedOn)='-', date(DealerToDealerInvoiceDate,'DD-MM-YYYY'), date(UpdatedOn,'DD-MM-YYYY')))as Retail_Year,

month(if(isnull(date(UpdatedOn)) or date(UpdatedOn)='-', date(DealerToDealerInvoiceDate,'DD-MM-YYYY'), date(UpdatedOn,'DD-MM-YYYY')))as Retail_Month,

//month(date(DealerToDealerInvoiceDate,'DD-MM-YYYY')) as Retail_Month,

//day(date(DealerToDealerInvoiceDate,'DD-MM-YYYY')) as Retail_Day,

day(if(isnull(date(UpdatedOn)) or date(UpdatedOn)='-'or date(UpdatedOn)='', date(CreatedOn,'DD-MM-YYYY'), date(UpdatedOn,'DD-MM-YYYY')))as Retail_Day,

'Q' & Ceil(Month(date(DealerToDealerInvoiceDate,'DD-MM-YYYY'))/3) as Retail_Quarter,

if(day(date(DealerToDealerInvoiceDate,'DD-MM-YYYY'))>=1 and day(date(DealerToDealerInvoiceDate,'DD-MM-YYYY'))<=7,1,

if(day(date(DealerToDealerInvoiceDate,'DD-MM-YYYY'))>=8 and day(date(DealerToDealerInvoiceDate,'DD-MM-YYYY'))<=14,2,

if(day(date(DealerToDealerInvoiceDate,'DD-MM-YYYY'))>=15 and day(date(DealerToDealerInvoiceDate,'DD-MM-YYYY'))<=21,3,4))) as Retail_Week,

'DealerToDealer' as RetailFlag,

ColorID,

// FinancerID,

// TaxRelationShipID,

// IsDelivered,

// IsDocsOK,

IsActive,

UnitPrice,

// BasicAmount,

// PaymentDetails,

// TypeOfInvoice,

// ReceivingDealerID,

IsCancelled ,

// IsDeleted,

// CreatedBy,

CreatedOn,

// UpdatedBy,

UpdatedOn,

//date(UpdatedOn,'DD-MM-YYYY') as UpdatedOn,

//count( if(UpdatedOn=='-','DealerToDealerInvoiceDate','UpdatedOn')) as count

//1

if(isnull(UpdatedOn) or UpdatedOn='-' , DealerToDealerInvoiceDate, UpdatedOn) as CountDate

//if(isnull(UpdatedOn) OR UpdatedOn='-','true','false') as updated_On_Date_Flag

//Pick( WildMatch(DealerToDealerInvoiceCode,'DI99*')) as KOKNEDEN

//if(WildMatch(DealerToDealerInvoiceCode,'IN35AJ115')=1,'Yes','No') as Group

// IsMigratedData,

// IsInward,

// IsIDMSMigratedData

FROM

$(QvdPath)\VES_DealerInvoiceForDealerToDealerSale.qvd

(qvd)

where year(DealerToDealerInvoiceDate)>=2007 and IsCancelled=0 and IsActive=-1 and not exists(DealerToDealerInvoiceCode,'*DI99*');

//where year(DealerToDealerInvoiceDate)>=2007 and IsCancelled=0 and IsActive=-1 and not (DealerToDealerInvoiceCode like'*DI99*');

//where year(DealerToDealerInvoiceDate)>=2007 and IsCancelled=0 and IsActive=-1 and WherenotWildMatch(DealerToDealerInvoiceCode,'*DI99*' );

//where year(DealerToDealerInvoiceDate)>=2007 and IsActive=-1 ;

//where year(DealerToDealerInvoiceDate)>=2007 and IsCancelled=0 and IsActive=-1;

Concatenate

LOAD

DealerID,

DealerInvoiceID,

DealerInvoiceCode,

OutletID,

SerialNumber,

// PurchaseOrderID,

VehicleID,

// SubModelTypeID,

// SubModelID,

date(DealerInvoiceDate,'DD-MM-YYYY') as DealerInvoiceDate,

// DealerID&'_'&(year(date(DealerInvoiceDate,'DD-MM-YYYY'))*10000+month(date(DealerInvoiceDate,'DD-MM-YYYY'))*100+day(date(DealerInvoiceDate,'DD-MM-YYYY'))) as RetailKey,

// DealerID&'_'&(year(date(DealerInvoiceDate,'DD-MM-YYYY'))*10000+month(date(DealerInvoiceDate,'DD-MM-YYYY'))*100+day(date(DealerInvoiceDate,'DD-MM-YYYY'))) as RetailKey2,

ChassisNumber,

DeliveryID,

'OnlyDealer' as RetailFlag,

//year(date(DealerInvoiceDate,'DD-MM-YYYY')) as Retail_Year,

year(if(isnull(date(UpdatedOn)) or date(UpdatedOn)='-', date(DealerInvoiceDate,'DD-MM-YYYY'), date(UpdatedOn,'DD-MM-YYYY')))as Retail_Year,

//month(date(DealerInvoiceDate,'DD-MM-YYYY')) as Retail_Month,

month(if(isnull(date(UpdatedOn)) or date(UpdatedOn)='-', date(DealerInvoiceDate,'DD-MM-YYYY'), date(UpdatedOn,'DD-MM-YYYY')))as Retail_Month,

//day(date(DealerInvoiceDate,'DD-MM-YYYY')) as Retail_Day,

day(if(isnull(date(UpdatedOn)) or date(UpdatedOn)='-' or date(UpdatedOn)='', date(CreatedOn,'DD-MM-YYYY'), date(UpdatedOn,'DD-MM-YYYY')))as Retail_Day,

'Q' & Ceil(Month(date(DealerInvoiceDate,'DD-MM-YYYY'))/3) as Retail_Quarter,

//week(EnquiryDate) as week,

DayNumberOfYear(DealerInvoiceDate) as TestNoOfdays,

if(day(date(DealerInvoiceDate,'DD-MM-YYYY'))>=1 and day(date(DealerInvoiceDate,'DD-MM-YYYY'))<=7,1,

if(day(date(DealerInvoiceDate,'DD-MM-YYYY'))>=8 and day(date(DealerInvoiceDate,'DD-MM-YYYY'))<=14,2,

if(day(date(DealerInvoiceDate,'DD-MM-YYYY'))>=15 and day(date(DealerInvoiceDate,'DD-MM-YYYY'))<=21,3,4))) as Retail_Week,

// EngineNumber,

ColorID,

// FinancerID,

// TaxRelationShipID,

// CustomerPurchaseOrderNumber,

//// IsDelivered,

//// IsDocsOK,

IsActive,

// UnitPrice,

// BasicAmount,

// TypeOfCustomer,

// PaymentDetails,

// TypeOfInvoice,

// CustomerID,

IsCancelled,

// IsDeleted,

// CreatedBy,

CreatedOn,

// UpdatedBy,

UpdatedOn,

//date(UpdatedOn,'DD-MM-YYYY') as UpdatedOn,

//if(isnull(UpdatedOn), DealerInvoiceDate, UpdatedOn) as CountDate,

//1

if(isnull(UpdatedOn) or UpdatedOn='-', DealerInvoiceDate, UpdatedOn)as CountDate

//2 if(isnull(UpdatedOn) OR UpdatedOn='-','true','false') as updated_On_Date_Flag

// Pick( WildMatch(DealerInvoiceCode,'DI99*')) as KOKNEDEN ,

//if(WildMatch(DealerInvoiceCode,IN53*),'Group1','Group2') as Group

//if(WildMatch(DealerInvoiceCode,'IN35AJ115')=1,'Yes','No') as Group

// IsMigratedData

// IsIDMSMigratedData

FROM $(TransactionQvdPath)\VES_DealerInvoice.qvd (qvd)

where year(DealerInvoiceDate)>=2007 and IsCancelled=0 and IsActive=-1 and not exists(DealerInvoiceCode,'*DI99*');

//where year(DealerInvoiceDate)>=2007 and IsCancelled=0 and IsActive=-1 and NOT(DealerInvoiceCode like '*DI99*' );

//where year(DealerInvoiceDate)>=2007 and IsActive=-1 ;

//where year(DealerInvoiceDate)>=2007 and IsCancelled=0 and IsActive=-1;

Retail:

load

DealerID

resident OrganisationMaster;

Left Join

load

'' as ret

Resident Dealer1;

//STORE Retail INTO C:\Users\winadmin\Desktop\Retail\tablenickname.qvd (qvd);

DROP Table Dealer1;

Can you please suggest how to take both of those table into one table.

Re: Merging two tables

evan_kurowski — Tue, 05 Aug 2014 04:02:44 GMT

You can merge any two tables within QlikView scripts by using the CONCATENATE keyword.

CONCATENATE(TABLE1)
LOAD * RESIDENT TABLE2;

This does not however attempt to perform joins based on any key system, CONCATENATE merely takes all the rows from TABLE2 and stacks them under the bottom of TABLE1, aligning the column names when they match, and creating new columns in the combined table when TABLE2 has added new field names to the mix.

Re: Merging two tables

amilafdo — Tue, 05 Aug 2014 04:36:18 GMT

Hi,

see the below eg:

Table Name:

Load Table1

from [ ] ;
Concatenate

Load Table2

from [ ];

Thanks

Amila

topic Merging two tables in QlikView

Merging two tables

Re: Merging two tables

Re: Merging two tables