topic Weird "load table" from excel, please help in QlikView

Weird "load table" from excel, please help

Mon, 04 Apr 2016 18:37:11 GMT

Hello!

This is my problem. I am trying to bring a new table from file, this case is an excel file, the issue comes with the format on witch the data is presented, this are different little tables, with the name of the "account" that it is suppose to consolidate all the data in the little table inside a bigger table, something like this:

Tittle: Employee benefits

Header 1	Header 2	Header 3	Header 4
x	x	x	x
x	x	x	x
x	x	x	x
x	x	x	x

Tittle: Office Expenses

Header 1	Header 2	Header 3	Header 4
x	x	x	x
x	x	x	x
x	x	x	x
x	x	x	x

Tittle: Employee benefits

Header 1	Header 2	Header 3	Header 4
x	x	x	x
x	x	x	x
x	x	x	x
x	x	x	x

So on...

As you can see, the issue is that the "Tittle" of every table is not added as an extra column, but rather as a field above each table little inside inside my excel sheet, I loaded the data and I haven't been able to turn the tittle in a new column so i can feed my entire consolidation table with a new column named "Tittle", something like this is what I should be getting:

Header 1	Header 2	Header 3	Header 4	Tittle
x	x	x	x	Employee benefits
x	x	x	x	Employee benefits
x	x	x	x	Office Expenses
x	x	x	x	Office Expenses

As you can see in my "result" table, the idea is to take all of those small tables and turn them into a huge consolidation table, where the new column named "Tittle", works to differentiate registers from one and other.

Thank you in advance, and I am also uploading an example file of the excel that I am using.

Re: Weird "load table" from excel, please help

satishkurra — Mon, 04 Apr 2016 18:58:21 GMT

Qlik cannot read the attached excel in a smooth way.

You better format the excel file and load the data

Re: Weird "load table" from excel, please help

swuehl — Mon, 04 Apr 2016 19:06:02 GMT

Maybe like

INPUT:
LOAD If(A = 'ACDOUNT CODE', C, Peek(AccountCode)) as AccountCode,
  If(A = 'ACCOUNT NAME', C, Peek(AccountName)) as AccountName,
  *;
LOAD A,
    B,
    C,
    D,
    E,
    F
FROM

(ooxml, no labels, table is Sheet1);




RESULT:
LOAD A as DATE,B as DC, C as DEC, D as CLASS, E as DEB,F as CR, AccountName,AccountCode
Resident INPUT
WHERE Len(Trim(B));


DROP TABLE INPUT;

Re: Weird "load table" from excel, please help

tamilarasu — Tue, 05 Apr 2016 02:55:16 GMT

Hi Jesus,

You can also use Transformation wizard to achieve the same.

Data:
LOAD Date(@1)         as Date,
  Num(@2)             as DC,
  @3                  as DEC,
  @4                  as CLASS,
  Num(@5,'#,##0.00')  as DEB,
  @6                  as CR,
  @7                  as [ACCOUNT CODE],
  @8                  as [ACCOUNT NAME]
FROM
[EXAMPLE_BS.xlsx]
(ooxml, no labels, table is Sheet1, filters(
ColXtr(3, RowCnd(CellValue, 1, StrCnd(contain, 'ACDOUNT CODE')), 0),
Replace(7, top, StrCnd(null)),
ColXtr(3, RowCnd(CellValue, 1, StrCnd(contain, 'ACCOUNT NAME')), 0),
Replace(8, top, StrCnd(null))
)) Where len(Trim(@2))>0 and @2<> 'DC';

QVW File attached for your sample data.