Merging Two tables into single one

deepakqlikview_123 — Thu, 26 Jun 2014 06:24:24 GMT

Hi All,

I have two tables as per below

EnquiryMaster1:

load

DealerID

resident OrganisationMaster;

left join(EnquiryMaster1)

LOAD

DealerID ,

EnquiryID,

EnquiryNumber,

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

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

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

year(EnquiryDate) as Year,

month(EnquiryDate) as Month,

day(EnquiryDate) as Day,

'Q' & Ceil(Month(EnquiryDate)/3) as Quarter,

//week(EnquiryDate) as week,

if(day(EnquiryDate)>=1 and day(EnquiryDate)<=7,1,

if(day(EnquiryDate)>=8 and day(EnquiryDate)<=14,2,

if(day(EnquiryDate)>=15 and day(EnquiryDate)<=21,3,4))) as Week,

OutletID,

EnquiryAtID,

EnquirySourceID,

// EnquiryReference,

ReasonForBuyingID,

// FollowupPeriodID,

CustomerID,

AreaID,

SubAreaID,

VehicleID,

SubModelTypeID,

SubModelID,

ColorID,

// Quantity,

FinancerID,

// FinancierDetails,

EndUseID,

SegmentID,

TargetCustomerTypeID as EnqTargetCustomerTypeID,

EnquiryStatusID,

EnquiryGeneratorEmployeeID,

EnquiryExecutorEmployeeID,

// IsSubModel,

DownPaymentCapacityID,

// OtherDetails,

FollowupRemarkID,

// ReasonID ,

// SubReasonID ,

// SubReasonTypeID as EnqSubReasonTypeID,

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

ReasonForPurchasingID,

FinanceTypeID

//if(FinanceTypeID=1,'Cash',

// if(FinanceTypeID=2,'Finance')) as FinanceType

// IsClosed,

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData,

// MeetingModuleid

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

where year(EnquiryDate)>=2007;

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);

EnquiryAtMaster:

LOAD

EnquiryAtID,

capitalize(EnquiryAtValue) as EnquiryAtValue

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData

FROM $(QvdPath)\PRE_EnquiryAtMaster.qvd (qvd);

EnquirySource_EnquiryAt:

LOAD

EnquirySourceEnquiryAtID,

EnquirySourceID

// EnquiryAtID

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData

FROM $(QvdPath)\PRE_EnquirySource_EnquiryAt.qvd (qvd);

EnquirySourceMaster:

LOAD

EnquirySourceID,

capitalize(EnquirySourceDescription) as EnquirySourceDescription

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData

FROM $(QvdPath)\PRE_EnquirySourceMaster.qvd (qvd);

Cal:

load

FinalDealerID,

CalDate,

VehicleID,

ColorID,

monthname(CalDate) as MonthName,

year(CalDate) as Year,

month(CalDate) as Month,

'Q' & Ceil(Month(CalDate)/3) as Quarter,

//week(CalDate) as Week1,

//week(month(CalDate)) as Week2,

day(CalDate) as Day,

(interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D')) as DayDiff,

FinalDealerID&'_'&VehicleID&'_'&ColorID&'_'&(year(CalDate)*10000+month(CalDate)*100+day(CalDate)) as BillingKey,

//FinalDealerID&'_'&(year(CalDate)*10000+month(CalDate)*100+day(CalDate)) as BillingKey1,

FinalDealerID&'_'&VehicleID&'_'&ColorID&'_'&(year(CalDate)*10000+month(CalDate)*100+day(CalDate)) as StockKey,

//FinalDealerID&'_'&(year(CalDate)*10000+month(CalDate)*100+day(CalDate)) as StockKey1,

if(day(CalDate)>=1 and day(CalDate)<=7,1,

if(day(CalDate)>=8 and day(CalDate)<=14,2,

if(day(CalDate)>=15 and day(CalDate)<=21,3,4))) as Week,

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=0 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=10),'0-10 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=11 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=20),'11-20 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=21 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=30),'21-30 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=31 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=40),'31-40 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=41 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=50),'41-50 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=51 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=60),'51-60 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=61 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=70),'61-70 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=71 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=80),'71-80 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=81 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=90),'81-90 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=91 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=180),'91-180 Days',

'>180 Days'

)))))))))) as DayAgeing

resident Cal1

where year(CalDate)>=2007;

drop Table Cal1;

Can you please suggest how to merge tables Enquirymster and Cal.

Thanks

topic Merging Two tables into single one in QlikView

Merging Two tables into single one