topic Re: Read Data from excel with merged columns in QlikView

Read Data from excel with merged columns

rodrigue_saade — Tue, 31 Mar 2015 15:47:50 GMT

Hi,

looking to the image below, you can see a screenshot of an excel table, having three columns merged under 1 column which is a date range. How can i read this file into Qlikview, to retrieve in the end three fields: the Name, the date ranges and the values (NC, DNP...)?

Thank you..

Re: Read Data from excel with merged columns

giakoum — Tue, 31 Mar 2015 15:55:16 GMT

if you load it without embedded labels, row 1 will also be loaded as a data line

you can then retrieve the date from there and drop the line

Re: Read Data from excel with merged columns

giakoum — Tue, 31 Mar 2015 15:59:45 GMT

see attached example

Re: Read Data from excel with merged columns

rodrigue_saade — Tue, 31 Mar 2015 16:00:27 GMT

Hi Ioannis,

and what about the merged fields under the date field?

how can i get them?

Re: Read Data from excel with merged columns

giakoum — Tue, 31 Mar 2015 16:03:18 GMT

if you mean columns B C D then see the example

Re: Read Data from excel with merged columns

rodrigue_saade — Tue, 31 Mar 2015 16:18:45 GMT

Hi Ioannis,

First, thank you for your prompt help.

Second, i tried the example you gave to me and i got the following result:

But what i need to retrieve in the end is the following result:

Also, we need to take into consideration that the date range columns are not fixed. I mean, at any time, a new date range column could be added, with the merged three fields under it etc. So, how can i do to read it dynamically?

Re: Read Data from excel with merged columns

giakoum — Tue, 31 Mar 2015 16:25:34 GMT

the 2nd line I do not understand : acc-s1 05 Apr - 09 Apr DNP

for acc-s1 and 05 Apr - 09 Apr all columns are empty. What do you expect DNP as action?

Also when you have 2 values for a date, should 2 lines be created? Like for acc-s1 29 Mar - 02 Apr, you have both NC and DNP.

Having it dynamically is not easy but an good start would be to use the crosstable function to change the columns to rows and then apply the logic.

Re: Read Data from excel with merged columns

rodrigue_saade — Wed, 01 Apr 2015 11:36:00 GMT

To give you a clearer picture of the result i want,

here is the data initially in the excel file:

I want typically the following result in Qlikview: