topic Re: Excel data not loading properly? in QlikView

Excel data not loading properly?

Thu, 07 Jan 2016 20:03:59 GMT

I have 10 tabs in one file that all have the exact same header/column title (Cost Center, Category, Jan, Feb, Mar, Apr, May...etc) but have different information within each tab. Example would be Tab A will provide only numbers for forecast, Tab B provides 2014 numbers, Tab C provides 2015 numbers, etc...

When I load it into the script though, Qlikview changes all the titles just to "ALLDATA". How do I fix this?

Re: Excel data not loading properly?

Thu, 07 Jan 2016 20:35:03 GMT

Try loading again by changing the label to none in file Wizard

Re: Excel data not loading properly?

Thu, 07 Jan 2016 21:38:14 GMT

It didn't work b/c when I load all 10 without label, they all have @1, @2, @3...etc in front of it and Qlikview connects them again.

Directory;

LOAD @1,

@2,

@3,

@4,

@5,

@6,

@7,

@8,

@9,

@10,

@11,

@12,

@13,

@14,

@15,

@16

FROM

[Flat Files\FF1 TOTAL OH.xlsx]

(ooxml, no labels, table is [2014], filters(

Remove(Row, Pos(Top, 3)),

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

Remove(Row, Pos(Top, 1))

));

Directory;

LOAD @1,

@2,

@3,

@4,

@5,

@6,

@7,

@8,

@9,

@10,

@11,

@12,

@13,

@14,

@15,

@16

FROM

[Flat Files\FF1 TOTAL OH.xlsx]

(ooxml, no labels, table is [2015], filters(

Remove(Row, Pos(Top, 3)),

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

Remove(Row, Pos(Top, 1))

));

Re: Excel data not loading properly?

maheshkuttappa — Thu, 07 Jan 2016 22:57:05 GMT

If field names are same Qlikview autoconcatenates the table in to single table. , if you want individual tables for each tab then you need to rename fields or use Qualify statements and unqualifiy key fields.

Eg: I am assuming cost center is Key field to connect the tables

QUALIFY *;

UNQUALIFY [Cost Center];

Tab1:

Load

[Cost Center], Category,Month

FROM

[test.xlsx]

(ooxml, embedded labels, table is Tab1);

Tab2:

Load

[Cost Center], Category,Month

FROM

[test.xlsx]

(ooxml, embedded labels, table is Tab2);

UNQUALIFY *;

Re: Excel data not loading properly?

Anonymous — Thu, 07 Jan 2016 23:10:36 GMT

It does not seem that you plan to combine all of these datasets into one table. Why are you not just creating 10 separate load statements and naming the tables in the process. It would seem that you would need to rename the fields too so that you do not have synthetic keys.

If you are combining all of the data into one table, create 10 load statements and put concatenate between each load statement and combine the data into one table.