topic Re: In Line in QlikView

In Line

upaliwije — Wed, 08 Mar 2017 08:28:50 GMT

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

Re: In Line

PrashantSangle — Wed, 08 Mar 2017 08:32:47 GMT

Maintain in excel and load excel.

Regards,

Prashant

Re: In Line

upaliwije — Wed, 08 Mar 2017 08:41:55 GMT

CAn you pls elaborate a little more

Re: In Line

jonathandienst — Wed, 08 Mar 2017 08:49:37 GMT

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

Re: In Line

PrashantSangle — Wed, 08 Mar 2017 08:56:20 GMT

Hi,

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

Regards,

Prashant

Re: In Line

upaliwije — Wed, 08 Mar 2017 08:57:54 GMT

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

MAKE1	MAKE
HONDA	HONDA
PERODUA	PERODUA
OTHERS	HONDA
OTHERS	PERODUA

Re: In Line

upaliwije — Wed, 08 Mar 2017 09:24:39 GMT

still the problem continues

Re: In Line

PrashantSangle — Wed, 08 Mar 2017 09:27:54 GMT

Hi,

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

Regards,

Prashant

Re: In Line

upaliwije — Wed, 08 Mar 2017 09:32:55 GMT

in Honda only, Some other categories falls under Others

Re: In Line

PrashantSangle — Wed, 08 Mar 2017 09:42:02 GMT

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

Re: In Line

avinashelite — Wed, 08 Mar 2017 09:44:10 GMT

Don't you have any key field with you could tag the values with ? because if others belong to all the types then how you will identify to which type it belongs ...their should be an identifier rite ?