Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rodrigue_saade
Contributor

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
Honored Contributor II

Re: Read Data from excel with merged columns

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
Honored Contributor II

Re: Read Data from excel with merged columns

see attached example

rodrigue_saade
Contributor

Re: Read Data from excel with merged columns

Hi Ioannis,

and what about the merged fields under the date field?

how can i get them?

giakoum
Honored Contributor II

Re: Read Data from excel with merged columns

if you mean columns B C D then see the example

rodrigue_saade
Contributor

Re: Read Data from excel with merged columns

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
Honored Contributor II

Re: Read Data from excel with merged columns

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
Contributor

Re: Read Data from excel with merged columns

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

Community Browser