topic Re: Loading Excel files with Row Header in QlikView

Loading Excel files with Row Header

abe786 — Sat, 16 Apr 2016 08:05:02 GMT

Hi folks,

I'm trying to load an excel file in Qlikview which has Customer Name in Row Headers, each file can have multiple number of customers and each customer can have any number of records. I've created a sample xlsx for my requirements, I'm not quite sure what is best way to load it in my script ?

Can anyone please help ?

Regards

Re: Loading Excel files with Row Header

MK_QSL — Sat, 16 Apr 2016 10:07:45 GMT

Input:
LOAD RowNo() as LineNO,
  A, 
     B
FROM
Workbook1.xlsx
(ooxml, explicit labels, table is Sheet1);


TempMaxLine:
Load Max(LineNO) as MaxLine Resident Input;
Let vTotalLine = Num(Peek('MaxLine',0,'TempMaxLine'));
Drop Table TempMaxLine;


Temp:
Load Trim(SubField(A,':',-1)) as Customer, A as OriginalCustomer Resident Input Where WildMatch(A,'*Customer*');


TempCount:
Load COUNT(DISTINCT Customer) as TotalCustomer Resident Temp; 
Let vTotalCustomer = Num(Peek('TotalCustomer',0,'TempCount'));


Drop Table TempCount;


For i=0 to $(vTotalCustomer)-1
  Let j1 = Peek('OriginalCustomer',$(i),'Temp');
  Let j2 = Peek('OriginalCustomer',$(i)+1,'Temp');
  TempLineNO:
  Load LineNO Resident Input Where Match(A,'$(j1)','$(j2)');
  TempMinMaxLineNO:
  Load Max(LineNO) as MaxLine, Min(LineNO) as MinLine Resident TempLineNO;
  Drop Table TempLineNO;
  Let vLineNOFrom = Num(Peek('MinLine',0,'TempMinMaxLineNO'));
  Let vLineNOTo = IF(Len('$(j2)')=0,'$(vTotalLine)'+1, RangeMax(Num(Peek('MaxLine',0,'TempMinMaxLineNO'))));
  Drop Table TempMinMaxLineNO;
  OutPut:
  Load A as Date, B as value, Trim(SubField('$(j1)',':',-1)) as Customer Resident Input Where LineNO > $(vLineNOFrom)+1 and LineNO < $(vLineNOTo);
Next i;


Drop Tables Input, Temp;


Let i = Null();
Let j1 = Null();
Let j2 = Null();
Let vLineNOFrom = Null();
Let vLineNOTo = Null();
Let vTotalCustomer = Null();

Re: Loading Excel files with Row Header

Kushal_Chawda — Sat, 16 Apr 2016 12:20:51 GMT

If you have more than two columns then add add that in mapping tables.

RenameFields:
mapping LOAD * Inline [
RenameFrom, RenameTo
A, Col 1
B, Col 2 ];

Data:
LOAD RecNo() as Rec,
if(WildMatch(lower(trim(A)),'customer*'), SubField(A,':',2)) as Customer,
*

FROM
[Workbook1.xlsx]
(ooxml, no labels, table is Sheet1);

New:
LOAD *,
if(WildMatch(lower(trim(A)),'customer*') or WildMatch(lower(trim(A)),'col*') ,1,0) as Flag;
LOAD *,
if(len(trim(Customer))=0,Peek('CustomerName'),Customer) as CustomerName
Resident Data
Order by Rec asc;

DROP Table Data;

DROP Fields Customer;

Final:
NoConcatenate
LOAD *
Resident New
where Flag=0;

DROP Table New;

DROP Field Flag;

RENAME Fields using RenameFields;

Re: Loading Excel files with Row Header

tamilarasu — Sat, 16 Apr 2016 12:30:38 GMT

Abeer,

Try,

Temp:
LOAD Date(@1) as Col1, 
     Num(@2) as Col2, 
     Replace(@3,'Customer: ','') as Customer
FROM
Workbook1.xlsx
(ooxml, no labels, table is Sheet1, filters(
ColXtr(1, RowCnd(CellValue, 1, StrCnd(contain, 'Customer')), 0),
Replace(3, top, StrCnd(null))
));

NoConcatenate
Data:
Load *
Resident Temp where Len(Trim(Col1))>0;

DROP Table Temp;

Sample QVW file attached for your data.

Re: Loading Excel files with Row Header

abe786 — Tue, 24 May 2016 07:35:56 GMT

Thanks all for the replies, though i'm sure all the solutions are correct, I used kushal's code to achieve my purpose