topic Trouble importing complex excel file in QlikView

Trouble importing complex excel file

Wed, 25 Nov 2020 16:16:04 GMT

I am having trouble importing this excel file in order to have the correct fields and values. This file is exported from an application our company uses and this is exactly the format of how it is exported. Since the data is going to be continuously changing and reloaded into Qlikview weekly, I cannot simply change things around in the excel file For every report, it is going to be categorized by a specified number of days, weeks, monthly, or quarterly based on a specified start date. In the case of this file, it is broken down weekly for 12 weeks beginning 5/1/2017. Under the weeks are four categories for hours. These hours relate to a specific person by project name. Next to the names are the totals of hours for each category of hours. This would not have to be included since Qlikview could do the work for us, but it is exported from the report in this format so it will remain in the file. If anyone could help me out it would be greatly appreciated! Please ask any questions as needed.

Re: Trouble importing complex excel file

beck_bakytbek — Mon, 22 May 2017 23:17:54 GMT

Hi Catlin,

i know, this is a bit another issue, but look at this, here you are able to find more ideas or maybe better solution for your business environment:

QlikTip #32: Exporting multiple QV objects to a single Excel document

I hope that helps

Beck

Re: Trouble importing complex excel file

marcus_sommer — Tue, 23 May 2017 05:16:04 GMT

Your data-source is a multi-header crosstable and there are already various postings and solutions for it, for example: multi_header_pivot_import.qvw

- Marcus

Re: Trouble importing complex excel file

Tue, 23 May 2017 13:52:07 GMT

This is helpful, but I am having a hard time putting my data into a pivot table because there is two different kinds of fields in column 1: People and Project Name. These need to be separate but also related to one another. For example, Project A's hours are associated with Jane Doe, but also Joe Doe.

Re: Trouble importing complex excel file

marcus_sommer — Wed, 24 May 2017 05:16:23 GMT

You could split this field into two fields by checking the content maybe like this:

if(mid(Column1, 1, 1) = ' ', previous(Column1), Column1) as Name,

if(mid(Column1, 1, 1) = ' ', trim(Column1), 'Total') as Project

There might be other identifier too which content a fieldvalue contained maybe the lenght or the comma-char or ...

- Marcus