topic Re: Removing string in QlikView

Removing string

deepakqlikview_123 — Mon, 19 May 2014 08:26:16 GMT

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

Re: Removing string

tresB — Mon, 19 May 2014 08:31:27 GMT

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

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

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

Re: Removing string

kiranmanoharrode — Mon, 19 May 2014 08:31:49 GMT

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

Re: Removing string

struniger — Mon, 19 May 2014 08:31:55 GMT

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

Re: Removing string

anbu1984 — Mon, 19 May 2014 08:33:31 GMT

Try this

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

Re: Removing string

ashfaq_haseeb — Mon, 19 May 2014 08:38:02 GMT

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

Re: Removing string

jagan — Mon, 19 May 2014 08:43:58 GMT

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.