Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Weird "load table" from excel, please help

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 1Header 2Header 3Header 4
xxxx
xxxx
xxxx
xxxx

Tittle: Office Expenses

Header 1Header 2Header 3Header 4
xxxx
xxxx
xxxx
xxxx

Tittle: Employee benefits

Header 1Header 2Header 3Header 4
xxxx
xxxx
xxxx
xxxx

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 1Header 2Header 3Header 4Tittle
xxxxEmployee benefits
xxxxEmployee benefits
xxxxOffice Expenses
xxxxOffice 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.

3 Replies
satishkurra
Specialist II
Specialist II

Qlik cannot read the attached excel in a smooth way.

You better format the excel file and load the data

swuehl
MVP
MVP

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;

tamilarasu
Champion
Champion

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';

Capture.PNG

QVW File attached for your sample data.