Removing string

[deepakqlikview_123]

Hello All,

Please find below code,

I would like to remove all strings in DealerToDealerInvoiceCode starting who starts with DI99

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

(qvd)

where year(DealerToDealerInvoiceDate)>=2007 and IsCancelled=0 and IsActive=-1 and WherenotWildMatch(DealerToDealerInvoiceCode,'DI99*' );

But wild match gives me error as  WildMatch is not valid function.

Can you please suggest how shall i proceed.

Thanks,

Deepak

[tresesco]

I guess it's because of space problem. Write like:

Where not WildMatch(DealerToDealerInvoiceCode,'DI99*' );

Spaces between 'where' and 'not', and 'not' and 'WildMatch'

[kiranmanoharrode]

can u give any example for a specific dealer, what actually do u want to do with string???

[struniger]

Change your WHERE Clause to

where

     year(DealerToDealerInvoiceDate)>=2007

and IsCancelled=0

and IsActive=-1

AND NOT(WildMatch(DealerToDealerInvoiceCode,'DI99*' ));

You have two WHERE's in there.

Good luck

Stefan

[anbu1984]

Try this

where year(DealerToDealerInvoiceDate)>=2007 and IsCancelled=0 and IsActive=-1 and not (WildMatch(DealerToDealerInvoiceCode,'DI99*' ));

[ashfaq_haseeb]

Hi,

try something like this.

Temp:
Load * inline
[
DealerToDealerInvoiceCode
DI991221
DI992122
DI992323
DJ99232
DJ993232

];
NoConcatenate
Main:
load *
Resident Temp
where not WildMatch(DealerToDealerInvoiceCode,'DI99*');
DROP Table Temp;

Regards

ASHFAQ

[jagan]

Hi Deepak,

Try this where condition

LOAD

*

FROM

(qvd)

where year(DealerToDealerInvoiceDate) >=2007 and IsCancelled=0 and IsActive=-1 and NOT WildMatch(DealerToDealerInvoiceCode,'DI99*' );

Regards,

jagan.