QlikView Extraction from xls.

[sona_sa]

Please find the attached xls and load to QlikView How we will do.

Thanks.

[sushil353]

Hi,

you need to do it separately for Actuals,Budget and opening balance using transformation steps and then concatenate them:

Code for Actuals is given below ..

Tabletest:
CrossTable(Year, Data, 2)
LOAD [GL Code],
[A/c Name],
[2010.000000],
[2011.000000],
[2012.000000],
[2013.000000],
[2010.0000001]
FROM
[Copy of DATA.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 8)),
Remove(Row, Pos(Top, 1)),
Transpose(),
Transpose(),
Replace(1, top, StrCnd(null))
));

Final:
LOAD *,'Actual' as Identifier
Resident Tabletest;

DROP Table Tabletest;

HTH

Sushil

[its_anandrjs]

Hi,

Try to load the tables in three parts

1. For Actual data and columns only and create a resident table.

2. Load data for only Budget and columns of budget only and create resident table.

3. Same for Opening Balance and its columns and create resident table.

For load script see this script

ActualData:
CrossTable(Year, Data, 2)
LOAD [GL Code],
[A/c Name],
[2009.000000],
[2010.000000],
[2011.000000],
[2012.000000],
[2013.000000]
FROM
DATA.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(
Remove(Col, Pos(Top, 17)),
Remove(Col, Pos(Top, 16)),
Remove(Col, Pos(Top, 15)),
Remove(Col, Pos(Top, 14)),
Remove(Col, Pos(Top, 13)),
Remove(Col, Pos(Top, 12)),
Remove(Col, Pos(Top, 11)),
Remove(Col, Pos(Top, 10)),
Remove(Col, Pos(Top, 9)),
Remove(Col, Pos(Top, 8))
));

Data:
LOAD *,'Actual' as Type Resident ActualData;
DROP Table ActualData;

BudgetData:
CrossTable(Year, Data, 2)
LOAD [GL Code],
[A/c Name],
[2009.000000],
[2010.000000],
[2011.000000],
[2012.000000],
[2013.000000]
FROM
DATA.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(
Remove(Col, Pos(Top, 7)),
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5)),
Remove(Col, Pos(Top, 4)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 12)),
Remove(Col, Pos(Top, 11)),
Remove(Col, Pos(Top, 10)),
Remove(Col, Pos(Top, 9)),
Remove(Col, Pos(Top, 8))
));

Concatenate(Data)
LOAD *,'Budget' as Type Resident BudgetData;
DROP Table BudgetData;


OpeningBal:
CrossTable(Year, Data, 2)
LOAD [GL Code],
[A/c Name],
[2009.000000],
[2010.000000],
[2011.000000],
[2012.000000],
[2013.000000]
FROM
DATA.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(
Remove(Col, Pos(Top, 12)),
Remove(Col, Pos(Top, 11)),
Remove(Col, Pos(Top, 10)),
Remove(Col, Pos(Top, 9)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 7)),
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5)),
Remove(Col, Pos(Top, 4)),
Remove(Col, Pos(Top, 3))
));

Concatenate(Data)
LOAD *,'Opening Bal' as Type Resident OpeningBal;
DROP Table OpeningBal;

Final:
LOAD *,Left(Year,4) as YearField Resident Data;
DROP Table Data;

Note:- This works some change in the excel file fill all blanks data with the corresponding previous value with [GL Code] in excel file.

Regards,

Anand

[jonathandienst]

Hi

This is a simpler approach that is also more general in that the number of years is not hard-coded into the script and no need to edit the XL file:

Data:

LOAD '@' & (RowNo() + 2) As FieldID,

  @1 As Type,

  Num(@2) As Year

FROM

ComplexLoad.xlsx

(ooxml, no labels, table is Sheet1, filters(

Transpose(),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

T_Data:

CrossTable(FieldID, Value, 5)

LOAD RowNo() As RowID,

  @1 As GLCode,

  @2 As ACName,

  *

FROM

ComplexLoad.xlsx

(ooxml, no labels, header is 2 lines, table is Sheet1, filters(

Replace(1, top, StrCnd(null))

));

Join (Data)

LOAD * Resident T_Data;

DROP Table T_Data;

DROP Fields @1, @2;

See attached.

HTH

Jonathan

[its_anandrjs]

Hi,

Another way for doing the same first load the table and then load cross table data with three tables and create the flag for the table and concatenate in final table and transform the Year field in the table.

Have a look the Script part

Raw:
LOAD [GL Code],
[A/c Name],
[2009],
[2010],
[2011],
[2012],
[2013],
[20091],
[20101],
[20111],
[20121],
[20131],
[20092],
[20102],
[20112],
[20122],
[20132],
if(IsNull([GL Code])  = -1, Peek('GL New Code'),[GL Code]) as [GL New Code]
FROM
DATA.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

tmpActualData:
CrossTable(Year, Data, 2)
LOAD [GL New Code],
[A/c Name],
[2009],
[2010],
[2011],
[2012],
[2013]
Resident Raw;    

Data:
LOAD *,'Actual' as Type Resident tmpActualData;
DROP Table tmpActualData;

tmpBudegetData:
CrossTable(Year, Data, 2)
LOAD [GL New Code],
[A/c Name],
[20091],
[20101],
[20111],
[20121],
[20131]
Resident Raw;    


Concatenate(Data)
LOAD *,'Budget' as Type Resident tmpBudegetData;
DROP Table tmpBudegetData;

tmpOpeningBalData:
CrossTable(Year, Data, 2)
LOAD [GL New Code],
[A/c Name],
[20092],
[20102],
[20112],
[20122],
[20132]
Resident Raw;    

Concatenate(Data)
LOAD *,'Opening Bal' as Type Resident tmpOpeningBalData;
DROP Table tmpOpeningBalData;
DROP Table Raw;

[Final Table]:
LOAD *,Left(Year,4) as YearField Resident Data;

DROP Table Data;

Regards

Anand

[Anonymous]

Hi,

Try below,

Temp_Load:
CrossTable(Year, Amount,2)
LOAD *
FROM
DATA.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(
Replace(1, top, StrCnd(null))
));


Data_Table:
LOAD
  [GL Code], 
    [A/c Name], 
    Left(Year,4) as Year, 
  If(Right(Year,1)=0, Amount) as Actual,
  If(Right(Year,1)=1, Amount) as Budget,
  If(Right(Year,1)=2, Amount) as [Opening Bal]
Resident Temp_Load;


DROP Table Temp_Load;