Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Please find the attached xls and load to QlikView How we will do.
Thanks.
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
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
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
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
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;