topic Re: Import Excel data thats in a weird format in App Development

Import Excel data thats in a weird format

lornafnb — Tue, 30 Nov 2021 16:25:32 GMT

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

Re: Import Excel data thats in a weird format

rwunderlich — Wed, 01 Sep 2021 22:08:31 GMT

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

Re: Import Excel data thats in a weird format

lornafnb — Thu, 02 Sep 2021 07:41:03 GMT

Excellent - much appreciated Rob!