Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Removing string

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


6 Replies
Highlighted
MVP
MVP

Re: Removing string

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

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

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

Highlighted
Creator III
Creator III

Re: Removing string

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

Highlighted
Creator
Creator

Re: Removing string

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

Highlighted
Master III
Master III

Re: Removing string

Try this

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

Highlighted

Re: Removing string

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

MVP & Luminary
MVP & Luminary

Re: Removing string

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.