topic Re: Help in QlikView

Help

panipat1990 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help

hector_munoz — Sun, 28 May 2017 20:52:55 GMT

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,

Re: Help

its_anandrjs — Sun, 28 May 2017 20:53:38 GMT

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

Re: Help

panipat1990 — Mon, 29 May 2017 04:46:27 GMT

Dear Sir,

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

Where showroom written i want to remove this

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

Re: Help

qlikview979 — Mon, 29 May 2017 05:13:35 GMT

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

Re: Help

Anil_Babu_Samineni — Mon, 29 May 2017 05:16:00 GMT

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

Re: Help

panipat1990 — Mon, 29 May 2017 06:36:45 GMT

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

Re: Help

panipat1990 — Mon, 29 May 2017 06:38:41 GMT

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

Re: Help

qlikview979 — Mon, 29 May 2017 06:40:23 GMT

how many records do you have in source file.

Re: Help

qlikview979 — Mon, 29 May 2017 06:50:35 GMT

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;

Re: Help

panipat1990 — Mon, 29 May 2017 06:52:38 GMT

1000 records in my field

Re: Help

qlikview979 — Mon, 29 May 2017 06:56:29 GMT

as per my knowledge

do like

If you have same names in this column use above code.

if you many new names in that column just insert that names in above condition and check and let me know.

Re: Help

panipat1990 — Mon, 29 May 2017 06:58:10 GMT

Not the same name all are different name

Re: Help

qlikview979 — Mon, 29 May 2017 07:02:48 GMT

Hi,

Remove Duplicates and Check in column how many unique names are there in column.

Re: Help

Anil_Babu_Samineni — Mon, 29 May 2017 07:21:53 GMT

May be this?