Dynamically import table from excel sheet

[#1]

Hi All,

I have an excel datasource in which data is coming in format, attached is the screenshot. I received this excel file on daily basis and want to pull data only from table containing table name "PQR". Since, the files are changing row number of that table is also changing.

So, how can I implement this in qlikview. Please help.

Thanks in Advance.

[tamilarasu]

Byron,

         I misunderstood question. I thought the OP wants to load data from second transaction. I guess the below one should work in that case.

Temp: 

LOAD 

RecNo() as DataStart 

From Format.xlsx 

(ooxml, No Labels, Table is Sheet1) 

WHERE A = 'Transaction2'; 

LET vHeaderSize = Peek('DataStart'); 

DROP TABLE Temp; 

Data: 

LOAD [Job ID] 

  ,[Job Name] 

  ,[Job Start Date] 

  ,[Job End Date] 

  ,[job status] 

From Format.xlsx 

(ooxml, embedded Labels, Header is $(vHeaderSize) Lines, Table is Sheet1) 

Where RecNo()= Autonumber([Job Name]);

I was trying to simplify the script.

[#3]

That's really clever with the where clause, I like that!

Without knowing how the data is populated I wonder what would be the best approach. From a simplicity perspective, your solution requires less lines of code to get the desired result. In my solution, more lines are required but the constraint is not on any dimensional value so you always guaranteed to get just transaction 2, unless transaction 3 name changes. Likewise with the RecNo() approach, if Job Name happens to be named the same for any transaction then incorrect records would get fetched. So the two solutions both have constraints, one at a 'table name' level and the other at the field level.

[tamilarasu]

I completely agree with you. But we don't have a choice for these kind of data. At first glance, I thought the same thing and was trying to find the first row and last row.