Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have in data table a field by the name of MAKE . I want to reclassify it the format given below and make new filed MAKE1
I used the load in line command to do this. But some of make are duplicated.
Eg: HONDA is categorized both under MAKE and MAKE1 fields. Can you pls tell me whether can use better method other than Load in Line
MAKE | MAKE1 |
PEUGEOT | PEUGEOT |
ASHOK LEYLAND | ASHOK LEYLAND |
NOMAD ZOTYE | NOMAD ZOTYE |
CHERRY QQ | CHERRY QQ |
HYHUNDAI | HYHUNDAI |
HONDA | HONDA |
TATA | TATA |
MAZDA | MAZDA |
PERODUA | PERODUA |
HUMBER | OTHERS |
CITROEN | OTHERS |
JOYLONG | OTHERS |
MAHENDRA | OTHERS |
FIAT | OTHERS |
OPEL | OTHERS |
FOTON | OTHERS |
ASHOK LEYLAND | OTHERS |
HYHUNDAI | OTHERS |
MATRIX | OTHERS |
Hi,
try something like this
Load Make,if(WildMatch(new,'*,*'),subfield(new,',',1),new) as final_make;
LOAD Make,
concat(Make1,',') as new
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet2)
group by Make;
Regards,
Prashant
Maintain in excel and load excel.
Regards,
Prashant
CAn you pls elaborate a little more
The problem with your data is that there is more than one Make1 value for some of the Make values (ASHOK LEYLAND, HYHUNDAI (HYUNDAI?))
Hi,
Instead of maintaining inline table, you can create excel file where you can maintain Make and Make1 classification.
Regards,
Prashant
Even if I corrected the duplicated records the issues appear as shown below
MAKE1 | MAKE |
HONDA | HONDA |
PERODUA | PERODUA |
OTHERS | HONDA |
OTHERS | PERODUA |
still the problem continues
Hi,
In this case where do you want to tag HONDA?? In HONDA / in OTHERS??
Regards,
Prashant
in Honda only, Some other categories falls under Others
Hi,
try something like this
Load Make,if(WildMatch(new,'*,*'),subfield(new,',',1),new) as final_make;
LOAD Make,
concat(Make1,',') as new
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet2)
group by Make;
Regards,
Prashant