Excel Data load in Qlikview

[Anonymous]

Hi all,

I have a task of loading the data from excel file in qlikview . ill tell you the complexity in it.

Consider i have two excel files, and each excel file is having 3 sections of data in it as follows (please verify with the attached sample excel sheet , here you can find Company Branch A,B,C as a keyword before table is started. so this might be a key to load)

Company Branch A

Company Branch B

Company Branch C

And here, i always want to load only the data of Company Branch B section,

Consider as an example in first file, Company Branch B section start from row 10 - 14 in excel sheet, so it should load only those 5 rows.

Consider as an example in second file, Company Branch B section start from row 50 - 150 in excel sheet, so it should load only those 100 rows.

Please let me know how can i do this.?

Thanks..

[Anil_Babu_Samineni]

If you are fine to create macro with in excel to delete rest things, You can go a head or

Please use conditional delete while loading data from excel

[Anonymous]

Hi anil,

I don't want to delete in the excel, and in qlikview i am already using conditional delete load,

example :

(ooxml, embedded labels, header is 2 lines, table is xxxx, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 164), Select(1, 0))),

Remove(Row, Pos(Top, 1)),

Remove(Row, RowCnd(Interval, Pos(Top, 269), Pos(Top, 341), Select(1, 0)))

)).

Here the thing is every month i get a file and every time i need to manually search in excel files from which row to which (Company Branch B section starts and ends), and then i have to script according to it and then concatenate all month files after that in qlikview. I don't want to do tat. It should be automated. Only just i need to load the file once that's it.

[vishsaggi]

Does your data comes in the same format? It doesnt matter where your Company Branch A, B, C are i mean does you data has only those 3 columns?

[antoniotiman]

Hi,

may be

Temp:
LOAD RowNo() as Rowno,
If(A='Company Branch B',1,If(Peek(Flag)= 1,If(Len(Trim(B)) > 0,1,0),0)) as Flag,
A,  B, C
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1297702-285258/SampleFile.xlsx"
(ooxml, no labels, table is Sheet1);
NoConcatenate LOAD A,B,C Resident Temp where Flag=1;
Drop Table Temp;

Regards,

Antonio

[Anonymous]

No, it may have many. Just for a example i kept only 3 in the excel..

[effinty2112]

Hi Kishore,

Try:

Table:

LOAD @1 as [Company Name],

     @2 as [Company code],

     @3 as DOJ,

     @4 as [Company Branch]

FROM

SampleFile.xlsx

(ooxml, no labels, table is Sheet1, filters(

ColXtr(1, RowCnd(CellValue, 1, StrCnd(start, 'Company Branch')), 0),

Replace(4, top, StrCnd(null)),

Remove(Row, RowCnd(CellValue, 2, StrCnd(null))),

Remove(Row, RowCnd(Compound,

RowCnd(CellValue, 3, StrCnd(equal, 'DOJ')),

RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0))

))

));

Then you can filter on Company Branch.

Cheers

Andrew

[Anonymous]

Thank you very much Andrew.. Can you please explain the filters. i feel difficult to understand.

[Anonymous]

Thank you antonio. Can you please explain the IF statement.

[antoniotiman]

If First at all Flag to 1 statement 'Company Branch B' or wath You want.

Then Flag to 1 until it finds B empty or Null (Len(Trim(B)) => 0). This is end of Company B Group.

Now until end flg to 0.

Load Resident only Flag = 1.