Forum Discussion
Solved
Help to consolidate Data into one sheet.
Hi, I have attached an example file and would really appreciate your help. I have explained what I want on the last sheet named consolidated data. thanks in advance.
How about this?
create temp table aa as
select udf_fillna_m(iif(f07 like 'Week:',f08,''),'x') Week,udf_fillna_m(iif(f07 like 'Order nr:',f08,''),'y') Order_nr,udf_fillna_m( iif(f07 like 'Customer:',f08,''),'z') customer, iif((f03 like '-') or (f03 is null),'',f03) f03,iif((f03 like '-') or (f03 is null),'',f04) f04,iif((f05 like '-') or (f05 is null),'',f05) f05,iif((f05 like '-') or (f05 is null),'',f06) f06,iif((f07 like '-') or (f07 is null) or ( regexp('Week|Order nr|Customer',f07)),'',f07) f07,iif((f07 like '-') or (f07 is null) or (regexp('Week|Order nr|Customer',f07)),'',f08) f08 from consolidateSheet;
select Week,Order_nr,customer,f04 Variety_Name,f03 Quantity from aa where f03!='' union all
select Week,Order_nr,customer,f06,f05 from aa where f05!='' union all
select Week,Order_nr,customer,f08 ,f07 from aa where f07!=''
