topic QlikView Extraction from xls. in QlikView

QlikView Extraction from xls.

sona_sa — Wed, 14 Jan 2015 07:35:19 GMT

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

Thanks.

Re: QlikView Extraction from xls.

sushil353 — Wed, 14 Jan 2015 07:47:51 GMT

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

Re: QlikView Extraction from xls.

its_anandrjs — Wed, 14 Jan 2015 08:26:21 GMT

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

Re: QlikView Extraction from xls.

jonathandienst — Wed, 14 Jan 2015 08:50:23 GMT

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

Re: QlikView Extraction from xls.

its_anandrjs — Wed, 14 Jan 2015 09:02:09 GMT

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

Re: QlikView Extraction from xls.

Anonymous — Wed, 14 Jan 2015 09:21:22 GMT

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;