topic Separating values in field in QlikView

Separating values in field

hopkinsc — Mon, 12 Feb 2018 21:39:23 GMT

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.

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?

Re: Separating values in field

rwunderlich — Tue, 13 Feb 2018 04:18:25 GMT

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

Re: Separating values in field

hopkinsc — Tue, 13 Feb 2018 08:31:14 GMT

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

rwunderlich — Wed, 14 Feb 2018 18:58:41 GMT

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

MarcoWedel — Thu, 15 Feb 2018 00:26:23 GMT

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