Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

In Line

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

  

MAKEMAKE1
PEUGEOTPEUGEOT
ASHOK LEYLANDASHOK LEYLAND
NOMAD ZOTYENOMAD ZOTYE
CHERRY QQCHERRY QQ
HYHUNDAIHYHUNDAI
HONDAHONDA
TATATATA
MAZDAMAZDA
PERODUAPERODUA
HUMBEROTHERS
CITROENOTHERS
JOYLONGOTHERS
MAHENDRAOTHERS
FIATOTHERS
OPELOTHERS
FOTONOTHERS
ASHOK LEYLANDOTHERS
HYHUNDAIOTHERS
MATRIXOTHERS
1 Solution

Accepted Solutions
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

10 Replies
PrashantSangle

Maintain in excel and load excel.

Regards,

Prashant

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
upaliwije
Creator II
Creator II
Author

CAn you pls elaborate a little more

jonathandienst
Partner - Champion III
Partner - Champion III

The problem with your data is that there is more than one Make1 value for some of the Make values (ASHOK LEYLAND, HYHUNDAI (HYUNDAI?))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
PrashantSangle

Hi,

Instead of maintaining inline table, you can create excel file where you can maintain Make and Make1 classification.

Regards,

Prashant

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
upaliwije
Creator II
Creator II
Author

Even if I corrected the duplicated records the issues appear as shown below

  

MAKE1MAKE
HONDAHONDA
PERODUAPERODUA
OTHERSHONDA
OTHERSPERODUA
upaliwije
Creator II
Creator II
Author

still the problem continues

PrashantSangle

Hi,

In this case where do you want to tag HONDA?? In HONDA / in OTHERS??

Regards,

Prashant

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
upaliwije
Creator II
Creator II
Author

in Honda only, Some other categories falls under Others

PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂