topic Re: Multiple headers in Excel in QlikView

Multiple headers in Excel

anat — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

My source file having multiple headers,can any one share me Extract Script based on my input data.

i have tried in many waves by referring Qlikview community links

Multiple header from Excel into Qlikview

multi_header_pivot_import.qvw

,but still i am facing problems.

Re: Multiple headers in Excel

anat — Wed, 20 Apr 2016 10:10:31 GMT

Attached source file

Re: Multiple headers in Excel

Wed, 20 Apr 2016 10:54:27 GMT

Hi Ananth

Can you please share a excel file to show the exact format you want the final result to be

Re: Multiple headers in Excel

anat — Wed, 20 Apr 2016 11:30:45 GMT

i want final result in below format.

Desc	Month	Val1	Val2	Val3
x	Apr-10	1	3	6
v	Apr-10	2	4	7
as	Apr-10	3	5	67
..	..	..	..	..
.	..	..	..	..

Re: Multiple headers in Excel

Wed, 20 Apr 2016 12:32:48 GMT

The format the data is in is very difficult to work with

Are you allowed to change the raw data to the format i've put in the file attached?

Re: Multiple headers in Excel

anat — Wed, 20 Apr 2016 15:39:11 GMT

Hi Chris,

Yes ,i can change date formats.

Re: Multiple headers in Excel

settu_periasamy — Thu, 21 Apr 2016 04:48:25 GMT

Hi,

May be check the attachment. (excel also) using multi_header_pivot_import.qvw

Re: Multiple headers in Excel

anat — Thu, 21 Apr 2016 09:51:06 GMT

Thanks Periasamy,

its working fine ,but in case if any values are null then its populating different values.

for Apr-10 month Val2 values are null for column Desc (x,as).

source data:

after loading the qvw ,its prompting below values.

Desc	Month_New	Value	Val1	Val2	Val3
as	Apr 2010		3	67	3
v	Apr 2010		2	4	7
x	Apr 2010		1	6	1

any other way to overcome this issue?

Re: Multiple headers in Excel

settu_periasamy — Thu, 21 Apr 2016 10:17:24 GMT

Yes.. i didn't notice that. When transformation in the script, if there is a null in the current column value, the previous value applied.

May be you can your null value as '0' and check, Like

Re: Multiple headers in Excel

anat — Thu, 21 Apr 2016 11:32:05 GMT

is any alternative to do in qlikview?

Re: Multiple headers in Excel

florentina_doga — Thu, 21 Apr 2016 14:17:15 GMT

hi ananth

try this

aa:

LOAD @1 as data,

@2 as val,

@3 as val1,

@4 as val2,

@5 as val3

FROM

[Copy of Book1-3.xlsx]

(ooxml, no labels, table is Sheet1, filters(

Replace(1, bottom, StrCnd(null)),

Transpose(),

Rotate(left),

Rotate(right),

Transpose(),

Replace(1, top, StrCnd(null))

));

bb:

load distinct

data, val as value

resident aa where upper(val)<>'DESC' and len(val)<>0;

left join(bb)

load

data, val as value, val1 as amount, 'x' as desc

resident aa where upper(val)<>'DESC' and len(val)<>0;

Concatenate(bb)

load

data, val as value, val2 as amount, 'y' as desc

resident aa where upper(val)<>'DESC' and len(val)<>0;

Concatenate(bb)

load

data, val as value, val3 as amount, 'as' as desc

resident aa where upper(val)<>'DESC' and len(val)<>0;

drop table aa;

hope this help.