fuzzy search and if condition

[0li5a3a]

Hi all,

I would like to extract some data from different columns which are similar like the attached files.I think a fuzzy search can help me.

I think I can use If condition and I want to achieve something like below:

tabtest:

load

Request,

Description,

Resolution,

Template,

if(Request = 'UIT','UIT' or  IF(Description = 'UIT') or F(Resolution = 'UIT') or F(Template = 'UIT')  as UIT,

IF(if(Request = 'Fluidone','Fluidone' or  IF(Description = 'Fluidone') or F(Resolution = 'Fluidone') or F(Template = 'UIT')  as Fluidone,

.......

from test.qvd

The above is part from my table and I want to create 4 extra columns and the load script would look like this:

Request	Description	Resolution	Template	UIT	Fluideone	EDMI	Sensus
UIT| 123	UIT| 123	UIT| 123	UIT| 123	UIT	-	-	-
1437	123 UIT	ABCD	UIT	UIT	-	-	-
ABBCC	ABCSDS UIT		CDDED	UIT	-	-	-
ABCSDS UIT	BDEF UIT	UIT	ABVC	UIT	-	-	-
12354	ABCDEFG UIT	4506	UIT	UIT	-	-	-
Fluidone| 123	Fluidone 123	Fluidone| 123	Fluidone| 123	-	Fluidone	-	-
1437	123 Fluidone	ABCD	Fluidone	-	Fluidone	-	-
ABBCCFluidone	ABCSDS		CDDED	-	Fluidone	-	-
ABCSDS Fluidone	BDEF Fluidone	Fluidone	ABVC	-	Fluidone	-	-
12354	ABCDEFG	Fluidone 4506		-	Fluidone	-	-
EDMI| 123	EDMI|| 123	| 123 EDMI|	EDMI| 123	-	-	EDMI	-
1437	123 EDMI|	ABCD	EDMI|	-	-	EDMI	-
ABBCC	ABCSDS	EDMI|	CDDED	-	-	EDMI	-
ABCSDS EDMI|	BDEF EDMI|		ABVC	-	-	EDMI	-
12354	ABCDEFG EDMI	4506	EDMI|	-	-	EDMI	-
Sensus abc		abc Sensus abc	abc Sensus	-	-	-	Sensus
asad	Sensus | abc	sda	scssc	-	-	-	Sensus
| abc	| abc		Sensus abc	-	-	-	Sensus
Sensus		Sensus | abc	Sensus abc	-	-	-	Sensus
eddaa	| abc  Sensus	sda	Sensus	-	-	-	Sensus

Thanks in advance.

[effinty2112 · Accepted solution]

Hi Constantin,

you could try:

Data:

Load*,

if(wildmatch(Request& Description & Resolution & Template,'*UIT*'), 'UIT') as UIT,

if(wildmatch(Request& Description & Resolution & Template,'*Fluideone*'), 'Fluideone') as Fluideone,

if(wildmatch(Request& Description & Resolution & Template,'*EDMI*'), 'EDMI') as EDMI,

if(wildmatch(Request& Description & Resolution & Template,'*Sensus*'), 'Sensus') as Sensus;

LOAD Request,

     Description,

     Resolution,

     Template

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Cheers

Andrew

[0li5a3a]

Thanks you Andrew.

[effinty2112]

Hi Constantin,

You're very Welcome!!