Read Data from excel with merged columns

[rodrigue_saade]

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..

[giakoum]

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

[giakoum]

see attached example

[rodrigue_saade]

Hi Ioannis,

and what about the merged fields under the date field?

how can i get them?

[giakoum]

if you mean columns B C D then see the example

[rodrigue_saade]

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?

[giakoum]

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.

[rodrigue_saade]

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: