Merging of fact tables

[deepakqlikview_123]

Hello team,

Below are my fact tables

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,

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

  day(date(DealerToDealerInvoiceDate,'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,

     ColorID,

//    FinancerID,

     'DealerToDealer' as RetailFlag,

  //   TaxRelationShipID,

  //   IsDelivered,

//    IsDocsOK,

//    IsActive,

     UnitPrice,

//     BasicAmount,

//     PaymentDetails,

//    TypeOfInvoice,

//    ReceivingDealerID,

     IsCancelled

//     IsDeleted,

//     CreatedBy,

//     CreatedOn,

//     UpdatedBy,

//     UpdatedOn,

//     IsMigratedData,

//     IsInward,

//     IsIDMSMigratedData

FROM

$(TransactionQvdPath)\VESPA_VES_DealerInvoiceForDealerToDealerSale.qvd

(qvd)

where year(DealerToDealerInvoiceDate)>=2007 and IsCancelled=0;

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,

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

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

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

  //week(EnquiryDate) as week,

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,

// IsMigratedData

// IsIDMSMigratedData

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

where year(DealerInvoiceDate)>=2007 and IsCancelled=0;

Retail:

load

DealerID

resident OrganisationMaster;

Left Join

load

*,

'' as ret

Resident Dealer1;

DROP Table Dealer1;

Final:

NoConcatenate

load * Resident Retail;

DROP Table Retail;

Concatenate(Final)

load * Resident

EnquiryMaster;

DROP table EnquiryMaster;

and second table is

Cal1:

load

BillingDealerID as FinalDealerID,

BillingVehicleID as VehicleID,

BillingColorID as ColorID,

date(BillingDate1,'DD-MM-YYYY') as CalDate

Resident Billing;

Concatenate

load

StockVehicleID as VehicleID,

StockDealerID as FinalDealerID,

StockColorID as ColorID,

date(VehicleReceiptDate,'DD-MM-YYYY') as CalDate

Resident Stock;

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',

'>90 Days'

))))))))) as DayAgeing

resident Cal1

where year(CalDate)>=2007;

drop Table Cal1;

If I am trying to concate nate above two tables I am not getting expected results.

And join is also not working can you please suggest how to handle this situation.

thanks,

Deepak

[vardhancse]

for joining any 2 tables, should have one common field so that based on that common field the join will happen.

We will do join basically to reduce the data volume,so that only required records will load

[deepakqlikview_123]

Can you please suggest how to work with above mentioned two tables to get data perfectly at output.

Thanks

[vardhancse]

Table names: table_1, table_2

ABC:

Load

A,

B,

C

from table_1;

left join(ABC)

Load

A,

D,

E

from table_2;

Based on common field A both tables will be joined.

so only records related to table_1 from table_2 will be loaded.