Separating values in field

[hopkinsc]

Hi all,

i have the attached excel file that has a field called ITEM, this field contains product codes but it also contains a category name.

eg

ITEM,                            Description

Small Animals

66764                             desc1

354656                           desc2

43577                             desc3

4124235436                   desc4

Food

4563                               desc5

23353434                        desc6

5554645                          desc7

236                                 desc8

etc

what i want is to separate the categories and make it a new field instead, so i would have a new field called 'Category' and in this new field i would have 'Small Animals', 'Food' etc. so the resulting table would be as follows

Category, ITEM, Description

Small Animals, 66764, desc1

Small Animals, 354656, desc2

Small Animals, 43577, desc3

Small Animals, 4124235436, desc4

Food, 4563, desc5

Food, 23353434, desc6

Food, 554645, desc7

Food, 236, desc8

a category would be anything in the ITEM field that has no value in the Description field.

can anyone help with this please?

[rwunderlich]

Temp:

LOAD

if(not IsNum(Item), Item, peek('Category')) as Category,

Item,

    Description,

    SubGrp,

    SubCat1,

    SubCat2,

    SubCat3,

    [Pack Size]

FROM

(ooxml, embedded labels, table is Sheet1);

Final:

NoConcatenate

LOAD

*

Resident Temp

Where Item <> Category

;

DROP Table Temp;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[hopkinsc]

Hi Rob,

thanks so much for your answer, unfortunately my sample data wasn't accurate!!

most of the ITEM numbers are alphanumeric

is there a way to achieve the same but the category being just text values?

i have updated the sample xls (attached)

[rwunderlich]

You just need to use a rule that differentiates Items from Categories.  For example, assume that Items always have at least one number and Categories never have a number.

if(not IsNum(keepchar(Item,'0123456789')), Item, peek('Category')) as Category,

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[MarcoWedel]

Hi,

maybe one solution might be:

table1:
LOAD Item,
    If(Previous(Len(Description)),Peek(Category),Previous(Item)) as Category,
    Description
FROM [https://community.qlik.com/servlet/JiveServlet/download/1434335-314459/Book2.xlsx] (ooxml, embedded labels, table is Sheet1)
Where Len(Description);

hope this helps

regards

Marco