Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
deepakqlikview_123
Specialist
Specialist

Fields not showing actual result


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

1 Solution

Accepted Solutions
mdmukramali
Specialist III
Specialist III

Dear,

Based on your condition your getting for

Zone 1 - 2457

Zone 2 - 2133

Zone 3 -  979

Zone 4 - 3934

View solution in original post

8 Replies
PradeepReddy
Specialist II
Specialist II

Hi Deepak,

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

Thansk,

Pradeep

mdmukramali
Specialist III
Specialist III

Dear Deepak,

Not able to open the file.

can you try to attach it again.

deepakqlikview_123
Specialist
Specialist
Author


Hi Pradeep pfa,I have shared data with qvw file

deepakqlikview_123
Specialist
Specialist
Author

Hi Mohammad,

I have just attached.

PFA

Thanks

PradeepReddy
Specialist II
Specialist II

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
Specialist
Specialist
Author

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
Specialist III
Specialist III

Dear,

Based on your condition your getting for

Zone 1 - 2457

Zone 2 - 2133

Zone 3 -  979

Zone 4 - 3934

deepakqlikview_123
Specialist
Specialist
Author

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