topic Re: Transform table with header & subheader in QlikView

Transform table with header & subheader

silvrwood — Fri, 19 Oct 2018 23:25:38 GMT

I have an Excel file (table) in which the first row contains the headers and the second row contains the years as a subheader, like this:

Library	# Visits	# Visits	# Visits	Avg Salary	Avg Salary	Avg Salary	Collection Size	Collection Size	Collection Size
	2008	2009	2010	2008	2009	2010	2008	2009	2010
ABC	50	60	70	10,000	10,000	12,000	5000	5000	5200
DEF	1000	1200	1500	20,000	21,000	21,500	10,000	15,000	16,000

I was able to get the year into its own column in Excel by transposing the data so the years were in columns, then creating a new worksheet with the library names and headers in their original positions, but with the year column from the transposed worksheet copied in, and then grouping the transposed data by year and individually transposing just the data for each year group into the new worksheet. I hope that makes sense. It was really confusing to work out, and it is confusing to remember how I did it. At all odds, I ended up with something like this:

Library	Year	#Visits	Avg Salary	Collection Size
ABC	2008	50	10,000	5000
ABC	2009	60	10,000	5000
ABC	2010	70	12,000	5200
DEF	2008	1000	20,000	10,000
DEF	2009	1200	21,5000	15,000
DEF	2010	1500	21,500	16,000

Can the transformation feature of QlikView do the same thing? If so, how?

Re: Transform table with header & subheader

swuehl — Sat, 20 Oct 2018 19:35:08 GMT

I don't think that the Qlik transformation can do this out of the box.

Have a look at

multi_header_pivot_import.qvw

Re: Transform table with header & subheader

Anil_Babu_Samineni — Mon, 22 Oct 2018 09:20:09 GMT

Can you check this way?

Sample:

CrossTable(FieldName, Data)

LOAD Library,

[# Visits],

[# Visits1],

[# Visits2],

[Avg Salary],

[Avg Salary1],

[Avg Salary2],

[Collection Size],

[Collection Size1],

[Collection Size2]

FROM

[https://community.qlik.com/message/1561304]

(html, utf8, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 2))

));

Join

LOAD @1 as Year

FROM

[https://community.qlik.com/message/1561304]

(html, utf8, no labels, table is @1, filters(

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 1)),

Rotate(left)

)) Where Len(@1)>0;

Final:

NoConcatenate

LOAD Library, FieldName as #Visits, Sum(Data) as Data, '#Visits' as Flag, Year Resident Sample Where WildMatch(FieldName,'#*')

Group By Library, FieldName, Year;

Concatenate

LOAD Library, FieldName as [Avg Salary], Sum(Data) as Data, 'Avg Salary' as Flag, Year Resident Sample Where WildMatch(FieldName,'Avg*')

Group By Library, FieldName, Year;

Concatenate

LOAD Library, FieldName as [Collection Size], Sum(Data) as Data, 'Collection Size' as Flag, Year Resident Sample Where WildMatch(FieldName,'Collection*')

Group By Library, FieldName, Year;

DROP Table Sample;

Re: Transform table with header & subheader

Anil_Babu_Samineni — Sat, 27 Oct 2018 03:36:10 GMT

Hello Mate, Can you see the thread.