Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Thanks you Andrew.
Hi Constantin,
You're very Welcome!!