Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
I don't think that the Qlik transformation can do this out of the box.
Have a look at
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;
Hello Mate, Can you see the thread.