if(not IsNum(Item), Item, peek('Category')) as Category,
(ooxml, embedded labels, table is Sheet1);
Where Item <> Category
DROP Table Temp;
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)
Book2.xlsx 9.1 K
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,
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
QlikCommunity_Thread_291144.qvw 150.8 K