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