Fields not showing actual result

[deepakqlikview_123]

Hello Team,

Please find attached qvw file.

There are four regions 1,2,3,4 The excepted values for regions for april 2014 are

Region 1-2461

Region 2-2166

Region 3-979

Region4 -3939

But it is not showing actual result,please suggest.

Thanks

[PradeepReddy]

Hi Deepak,

Not able to open the attached QVW file. If possible, can you please share the data and the requirements?

Thansk,

Pradeep

[mdmukramali]

Dear Deepak,

Not able to open the file.

can you try to attach it again.

[deepakqlikview_123]

Hi Pradeep pfa,I have shared data with qvw file

[deepakqlikview_123]

Hi Mohammad,

I have just attached.

PFA

Thanks

[PradeepReddy]

Deepak,

The QVW file is almost 80MB...   It is difficult to download for anyone. Please try to upload the doc with the less volume of data and without any desigining stuff, place the requiremnt only...

[deepakqlikview_123]

Hi all,

Actrually file size was too huge so i have reduced the size contents of files are important so that i have attached this.

Thanks

[mdmukramali · Accepted solution]

Dear,

Based on your condition your getting for

Zone 1 - 2457

Zone 2 - 2133

Zone 3 -  979

Zone 4 - 3934

[deepakqlikview_123]

Hi Mohammad,

Can you please suggest what would be trhe condition.

While applying filter we considered following

IsCancelled is set to 0,isactive is set to -1 Moreover also updatedon and createdon ddate and dealer invoice code is set properly.

Because all conditions in Qlikview  based on above mentioned field.

Code is as per below

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;

Thanks