Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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)
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
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