Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

Re: Read Data from excel with merged columns

see attached example

Highlighted
Partner
Partner

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?

Highlighted
Partner
Partner

Re: Read Data from excel with merged columns

if you mean columns B C D then see the example

Highlighted
Partner
Partner

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?

Highlighted
Partner
Partner

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.

Partner
Partner

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