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

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#3]

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

ACCOUNT    Client    Balance

For instance, is the below code doing?

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

Thanks in advance,

Olle

[Gysbert_Wassenaar]

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.

[#5]

It works now, thanks to your explanation.

Thanks a lot Gysbert!!!

Rgds,

Olle

[#6]

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

[Gysbert_Wassenaar]

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.

[#8]

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

[#9]

Hi again,

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

LOAD*, RecNo() as RowID

Thanks,

Olle