Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
hopkinsc
Valued Contributor II

Separating values in field

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?

4 Replies

Re: Separating values in field

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
Valued Contributor II

Re: Separating values in field

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)

Re: Separating values in field

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

Re: Separating values in field

Hi,

maybe one solution might be:

QlikCommunity_Thread_291144_Pic1.JPG

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

Community Browser