Loading Excel files with Row Header

[abe786]

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

[MK_QSL]

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();

[Kushal_Chawda · Accepted solution]

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;

[tamilarasu]

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.

[abe786]

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