Import Excel File with CrossTable Function

[#1]

Dear Ladies and Gentlemen,

I've a complex Excel File (well there hundrets of this) and I need to import this to Qliview.

I've tried with CrossTable functionality, but couldn't solve.

PLEASE, can any help me on this. I've searched for this topic in the whole community, but couldn't find anything similar to this.[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/4527.test1forcommunity.xlsx:550:0]

[#2]

Seems to too difficult, even for the most advanced onces, huh?

I struggled with it for three days.

[prieper]

What do you expect to see?
Would also be helpful, if you would paste an Excel rather than a picture ....
Have you tried the wizard already, you might come to a solution like

CrossTable(x, y, 4)
LOAD status,
 material,
 blabla,
 MAG,
 F5,
 F6,
 F7,
 F8
FROM
[.......]
(biff, embedded labels, header is x lines, table is something$);

HTH
Peter

[stevedark]

Hi Daniel,

It is hard to tell from an image, but it would appear that you need to do a number of different loads from the spreadsheet. You will need to limit to certain areas of the spreadsheet each time, using the 'header is' and First n statements in the load. Often with these types of sheets you end up needing to read parts of it and then use Peek or Lookup to read specific cells from the output.

As Peter rightly says an upload of an example spreadsheet and a definition of what you are trying to achieve would help people to be able to suggest a suitable solution.

Regards,
Steve

[#5]

Sorry guys, I thought I attached a Excel-file.

So here it is:[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/3580.List_5F00_forCommunityQ.xlsx:550:0]

[#6]

Dear Peter,

Dear Steve,

thanks for your comment,

I'm quite familiar with the wizard, but this Table I could not solve.

It's an standard output out of SAP and I have about 1000 Excel-Files to import.

[prieper]

Hi Daniel,

what is your required data in the sheet? Resp. which data shall be shown?
Are the columns fixed?

Agree with Steve that you will need mulitple load in order to identify the different headers and data.

Peter

[pljsoftware]

Hi,

can you explain how must to be your linear table loaded by excel file?

Try to make your table in more steps

[#9]

Hello Peter,

this should be the desired input for Qlikview. Sorry, for attaching everything as a bitmap, but appending it as a html table

was not that nice.

[stevedark]

Hi Daniel,

The wizard will give you a guide for pulling the file in - but you need to go slightly beyond that for what you are trying to achieve.

To pull in all the files you will need to do a loop through a list of all files in a folder matching a mask. This will allow you to read in header fields and place them into variables to insert in as columns when reading the rest of the data.

The following code is a simplified version of where I have done this for a client. I think with a bit of modification you should be able to get it to work for your spreadsheets.

let vSourceFolder = vSourceFolder & if(right(vSourceFolder, 1) <> '\', '\', '');For each vFileName in filelist(vSourceFolder & vFileMask) if index(vFileName, '~$') = 0 then // ~$ denotes an open file temp file Temp_Headers: FIRST 10 LOAD lower(replace(replace(@4, ':', ''), ' ', '')) as HeaderName, trim(@5) as HeaderValue FROM [$(vFileName)] (biff, no labels, table is SourceData$); let vBusinessUnit = lookup('HeaderValue', 'HeaderName', 'businessunit'); let vReportingUnit = lookup('HeaderValue', 'HeaderName', 'reportingunit'); DROP TABLE Temp_Headers; RTData: LOAD filename() as [Source File], date(filetime(), 'DD MMM YYYY hh:mm') as [Source File Updated], '$(vBusinessUnit)' as [Business Unit], '$(vReportingUnit)' as [Reporting Unit], @4 as [Currency], @5 as [Amount] FROM [$(vFileName)] (biff, no labels, header is 9 lines, table is SourceData$) end ifNext

Note in this example I am always using column references rather than names - this is because the column names were not consistent between the sheets (random spaces). Similarly, the header names are cleaned up as these were inconsistent between spreadsheets (again spaces and capitalisation etc.). The source folder and file mask variables are set up outside of the load script. I think everything else is pretty much self explanatory.

I hope that this gives you what you need to craft your own load script.

Regards,
Steve