Help

[panipat1990]

Dear Team,

I stuck a requirement.Kindly help me for solving this issue.

For this i am sharing a excel. where field indicate data and output i want this

Input

Adishwar Auto Diagnostics - PRE Owned Showroom

Adishwar Auto Diagnostics Pvt. Ltd. - Showroom

Showroom - Chandigarh

Output

Adishwar Auto Diagnostics

Adishwar Auto Diagnostics Pvt. Ltd.

Chandigarh

Kindly help me

[hector_munoz]

Hi Sikka,

I assume you need to clear a field composed by two parts separated by '-'. Exactly, you need to remove all reference to "Showroom"... So, try this:

If(SubstringCount(SubField(<input_field>, '-', 1), 'Showroom') > 0, Trim(SubField(<input_field>, '-', 2)), Trim(SubField(<input_field>, '-', 1))) AS new_field

Hope it serves!

Regards,

H

[its_anandrjs]

Try this with SubField function

LOAD *,

if(left(Input_Field,4) = 'Show',Trim(SubField(Input_Field,'-',-1)),Trim(SubField(Input_Field,'-',1))) as Output;

LOAD * Inline

[

Input_Field

Adishwar Auto Diagnostics - PRE Owned Showroom

Adishwar Auto Diagnostics Pvt. Ltd. - Showroom

Showroom - Chandigarh

];

Note:- Be insure in the field Input_Field the first character is Showroom then use the script as provided to you above.

Regards,

Anand

[panipat1990]

Dear Sir,

My actual input is I am sharing excel where input and output both exist.

Where showroom written i want to remove this

Like

  Input_Data

Aarti-ROHTAK

Sidwani-BHIWANI

RAJU Auto Diagnostics - PRE Owned Showroom

SIKKA Auto Diagnostics Pvt. Ltd. - ASC ROHTAK_DNU

SUNEJA Auto Diagnostics Pvt. Ltd. - RAM Motors

SOMIKA Auto Diagnostics Pvt. Ltd. - Showroom

SHOWROOM-REWARI

Output:

Aarti-ROHTAK

Sidwani-BHIWANI

RAJU Auto Diagnostics

SIKKA Auto Diagnostics Pvt. Ltd. - ASC ROHTAK_DNU

SUNEJA Auto Diagnostics Pvt. Ltd. - RAM Motors

SOMIKA Auto Diagnostics Pvt. Ltd.

REWARI

[qlikview979]

How many records you have in your source file like that.

[Anil_Babu_Samineni]

May be one solution this?

LOAD Input_Data, Output_Data Where Len(Output_Data)>0;

LOAD Input_Data, IF(not SubStringCount(Output_Data,'Showroom'),Output_Data)  as Output_Data;

LOAD Input_Data, Output_Data Where not WildMatch(Output_Data,'SHOW*', ' SHOW*');

LOAD Input_Data, SubField(Input_Data,'-') as Output_Data Inline [

Input_Data

Aarti-ROHTAK

Sidwani-BHIWANI

RAJU Auto Diagnostics - PRE Owned Showroom

SIKKA Auto Diagnostics Pvt. Ltd. - ASC ROHTAK_DNU

SUNEJA Auto Diagnostics Pvt. Ltd. - RAM Motors

SOMIKA Auto Diagnostics Pvt. Ltd. - Showroom

SHOWROOM-REWARI

];

Updated Attached

[panipat1990]

Output field is not my data it is required i want to output

[panipat1990]

in excel input is my data and output i want to required

[qlikview979]

how many records do you have in source file.

[qlikview979]

May be ,this is help full to you

INPUT:

load * inline [

INPUT

Aarti-ROHTAK

Sidwani-BHIWANI

RAJU Auto Diagnostics - PRE Owned Showroom

SIKKA Auto Diagnostics Pvt. Ltd. - ASC ROHTAK_DNU

SUNEJA Auto Diagnostics Pvt. Ltd. - RAM Motors

SOMIKA Auto Diagnostics Pvt. Ltd. - Showroom

SHOWROOM-REWARI

];

NoConcatenate

OUTPUT:

LOAD

INPUT,

if(INPUT='Aarti-ROHTAK','Aarti-ROHTAK',if(INPUT='Sidwani-BHIWANI','Sidwani-BHIWANI',

if(INPUT='RAJU Auto Diagnostics - PRE Owned Showroom','RAJU Auto Diagnostics',if(INPUT='SIKKA Auto Diagnostics Pvt. Ltd. - ASC ROHTAK_DNU','SIKKA Auto Diagnostics Pvt. Ltd. - ASC ROHTAK_DNU',

if(INPUT='SUNEJA Auto Diagnostics Pvt. Ltd. - RAM Motors','SUNEJA Auto Diagnostics Pvt. Ltd. - RAM Motors',

if(INPUT='SOMIKA Auto Diagnostics Pvt. Ltd. - Showroom','SOMIKA Auto Diagnostics Pvt. Ltd',if(INPUT='SHOWROOM-REWARI','REWARI'))))))) as OUTPUT

Resident INPUT;

DROP Table INPUT;