topic Re: Excel Data load in Qlikview in QlikView

Excel Data load in Qlikview

Anonymous — Thu, 29 Jun 2017 14:04:56 GMT

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..

Re: Excel Data load in Qlikview

Anil_Babu_Samineni — Thu, 29 Jun 2017 14:08:54 GMT

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

Re: Excel Data load in Qlikview

Anonymous — Thu, 29 Jun 2017 14:17:19 GMT

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.

Re: Excel Data load in Qlikview

vishsaggi — Thu, 29 Jun 2017 15:01:39 GMT

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?

Re: Excel Data load in Qlikview

antoniotiman — Thu, 29 Jun 2017 15:18:39 GMT

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

Re: Excel Data load in Qlikview

Anonymous — Thu, 29 Jun 2017 15:31:45 GMT

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

Re: Excel Data load in Qlikview

effinty2112 — Thu, 29 Jun 2017 15:44:42 GMT

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

Re: Excel Data load in Qlikview

Anonymous — Thu, 29 Jun 2017 15:50:55 GMT

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

Re: Excel Data load in Qlikview

Anonymous — Thu, 29 Jun 2017 15:51:40 GMT

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

Re: Excel Data load in Qlikview

antoniotiman — Thu, 29 Jun 2017 16:03:43 GMT

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.

Re: Excel Data load in Qlikview

effinty2112 — Thu, 29 Jun 2017 16:05:57 GMT

Hi Kishore,

In the File Wizard go through the Transformation Steps.

The steps I took were

1. Add a new column. copied column 1 where it started with Company Branch

2. Fill column 4 with cell condition empty from above.

3. Delete rows where column 2 is empty.

4. Delete rows where

condtion 1 : column 3 = DOJ

then add condition 2:

Start from row 2 from top, to row 1 from bottom (second row to last row), Select 1, Skip 0

Rename your columns in the wizard on into the script when finished.

The only way to learn to transform these flat files is to work with the wizard. It may be tricky at first but you will figure it out.

cheers

Andrew