Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
rodrigue_saade
Partner - Creator
Partner - Creator

Read Data from excel with merged columns

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

download.png

Thank you..

7 Replies
giakoum
Partner - Master II
Partner - Master II

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
Partner - Master II
Partner - Master II

see attached example

rodrigue_saade
Partner - Creator
Partner - Creator
Author

Hi Ioannis,

and what about the merged fields under the date field?

how can i get them?

giakoum
Partner - Master II
Partner - Master II

if you mean columns B C D then see the example

rodrigue_saade
Partner - Creator
Partner - Creator
Author

Hi Ioannis,

First, thank you for your prompt help.

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

down2.PNG

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

down3.PNG

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
Partner - Master II
Partner - Master II

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
Partner - Creator
Partner - Creator
Author

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

here is the data initially in the excel file:

initially.png

I want typically the following result in Qlikview:

endresult.png