Tables

deepakqlikview_123 — Fri, 27 Jun 2014 07:13:23 GMT

Hi all,

Below are my two tables

Cal:

load

DealerID,

CalDate,

VehicleID,

ColorID,

monthname(CalDate) as MonthName,

year(CalDate) as Billing_Year,

month(CalDate) as Billing_Month,

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

//week(CalDate) as Week1,

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

day(CalDate) as Billing_Day,

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

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

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

DealerID&'_'&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 Billing_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;

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)\VESPA_PRE_EmployeeMaster.qvd

(qvd);

//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)\VESPA_PRE_EnquiryStatus.qvd

(qvd);

EnquiryAtMaster:

LOAD

EnquiryAtID,

capitalize(EnquiryAtValue) as EnquiryAtValue

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

Can you please suggest how shall i proceed.It gives synthetic key

there are 4-5 fields comman.

Shall I use join or concatenate.

If so what will be syntax for that.

Thanks,

Deepak

// IsMigratedData,

// IsIDMSMigratedData

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

EnquirySource_EnquiryAt:

LOAD

EnquirySourceEnquiryAtID,

EnquirySourceID

// EnquiryAtID

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData

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

topic Tables in QlikView

Tables