topic Re: Re: Reading data from document with multiple sections (and multiple column headers) in QlikView

Reading data from document with multiple sections (and multiple column headers)

Wed, 10 Sep 2014 08:21:53 GMT

Hi,

I'm trying to read data from an excel report with an inconvenient format.

For each date the report has one section with new headers, causing problems when loading it into qlikview.

Any clues?

Sample report and sample QV doc attached.

Thanks in advance,

Olle

Re: Reading data from document with multiple sections (and multiple column headers)

Gysbert_Wassenaar — Wed, 10 Sep 2014 09:02:52 GMT

See attached qvw.

Re: Re: Reading data from document with multiple sections (and multiple column headers)

Wed, 10 Sep 2014 11:41:10 GMT

Thanks for your reply Gysbert.

Looks great, however I'm not a 100% sure how your solution works.

When I tried to apply it to my master doc I got the below result at the top of my tablebox.

ACCOUNT Client Balance
ACCOUNT Client Balance

ACCOUNT Client Balance

For instance, is the below code doing?

WHERE Client <> 'Client' and len(trim(ACCOUNT)) ;

Thanks in advance,

Olle

Re: Re: Reading data from document with multiple sections (and multiple column headers)

Gysbert_Wassenaar — Wed, 10 Sep 2014 11:52:53 GMT

The where clause makes sure that only those records are loaded where the value in the Client column does not equal the string value 'Client', i.e. it's a header row, and also where the ACCOUNT field contains a value that is at least 1 character long. Trim with strip of any leading and trailing spaces and len will calculate the lenght. If len(trim(ACCOUNT)) is 0 then it does not contain a value. Those would be the rows that contain only a data in the Client column.

Re: Re: Reading data from document with multiple sections (and multiple column headers)

Wed, 10 Sep 2014 12:19:22 GMT

It works now, thanks to your explanation.

Thanks a lot Gysbert!!!

Rgds,

Olle

Re: Re: Re: Reading data from document with multiple sections (and multiple column headers)

Wed, 10 Sep 2014 13:35:24 GMT

Hi again Gysbert.

When loading from multiple excel docs like below. I get synthetic keys.

FROM

(biff, embedded labels, header is 2 lines, table is @1)
WHERE [Participant BIC] <> 'Participant BIC' and len(trim([Account number]));

Do you know why? Something to do with the NoConcatenate in the 'Result' table maybe?

Thanks in advance,

Olle

Re: Reading data from document with multiple sections (and multiple column headers)

Gysbert_Wassenaar — Wed, 10 Sep 2014 14:07:32 GMT

Make sure you load the exact same fields from the excel files. Otherwise you'll get several tables and synthetic keys. You may want to load all the data from the excel files into a temporary database first and then process that table to add the date field in fill in the missing values in the timestamp field. Don't forget to drop the temporary table at the end.

Re: Re: Reading data from document with multiple sections (and multiple column headers)

Thu, 11 Sep 2014 09:08:47 GMT

Hi Gysbert,

I did a test duplicating the sample excel files and modifying the content (format and columns remains the very same).

Got the below result (see pic).

However, when removing the formatting parts (timestamp, Date etc) it worked, just one table, no synthetic keys! So just like you said I need some solution to fix/complement the data before putting togehther the result table.

What do you think would be the easiest solution in my case?

Thanks in advance,

Olle

Re: Re: Reading data from document with multiple sections (and multiple column headers)

Thu, 11 Sep 2014 09:24:09 GMT

Hi again,

I think I found the problem. I removed the below and no more synthetic keys

LOAD*, RecNo() as RowID

Thanks,

Olle