Import Excel data thats in a weird format

[lornafnb]

hi guys

the beauty of importing data from Excel into Qlik.

Please find attached Excel file - 2 sheets - 1st sheet is how data currently looks

2Nd Sheet is how I need it to look to consume.

All help appreciated.

Thanks

[rwunderlich · Accepted solution]

How about:

Raw:
LOAD
  if(IsText(@1), @1, peek('Fruit')) as Fruit,
  num(@1) as Period,
  num(@2) as Qty,
  num(@3) as UnitRate,
  num(@4) as Price
FROM
lib://temp/Fruit.xlsx
(ooxml, no labels, table is [Fruit - Raw Data], filters(
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5)),
Unwrap(Col, Pos(Top, 5))
))
Where @1 <> 'Period'
;

Final:
NoConcatenate
LOAD
 *
Resident Raw
Where len(Period)
;
Drop Table Raw;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[lornafnb]

Excellent - much appreciated Rob!