Hi all,
I'm looking to load some data that is from a nested crosstable. I have followed the instructions from this link (https://community.qlik.com/docs/DOC-15998) but I think my data having some repeated fields is making it fail to properly load. (I've changed the values to protect the data set, however there are still blanks in some of the cells). Sample below.
I should note that the [myStudents] table does contain the correct data, it just doesn't join with the [Pivot] table properly. How do I get it to join properly?
| 2010 | 2010 | 2010 | 2011 | 2011 | 2011 |
Year level | Male | Female | Total | Male | Female | Total |
Primary | 1000 | 2000.6 | 3000.6 | 1000 | 3000.8 | 4000.8 |
Prep | 200 | 300 | 500 | 321.6 | 432.3 | 753.9 |
Year 1 | 300 | 400.9 | 700.9 | 366 | 300 | 666 |
Year 2 | 340.6 | 222.4 | 563 | 400 | 500.6 | 900.6 |
Primary Ungraded | | 3 | 3 | 1 | | 1 |
Here is the load script I'd use for this clipped table, built following the above link.
[genderYear]:
LOAD
[@1],
[@2],
[@3],
[@4],
[@5],
[@6],
[@7],
FROM [myData.csv]
(txt, codepage is 28591, no labels, delimiter is ',', msq)
;
[Pivot]:
CrossTable(ColumnName,Year)
LOAD
[@1] as notNeeded,
[@2],
[@3],
[@4],
[@5],
[@6],
[@7],
resident [genderYear]
Where trim([@1]) = '' // get single row of Year values
;
drop field notNeeded;
[PivotGender]:
CrossTable(ColumnName,Gender)
LOAD
[@1] as notNeeded,
[@2],
[@3],
[@4],
[@5],
[@6],
[@7],
resident [genderYear]
where [@1] = 'Year level' //get single row of year levels
;
drop field notNeeded;
left join ([Pivot])
LOAD
ColumnName,
Gender
resident [PivotGender];
drop table [PivotGender];
[myStudents]:
CrossTable(ColumnName,Students)
LOAD
[@1] as YearLevel,
[@2],
[@3],
[@4],
[@5],
[@6],
[@7],
resident [genderYear]
where trim([@1]) <> '' AND [@1] <> 'Year level';
right join [Pivot]:
Load
ColumnName,
Students,
YearLevel
resident [myStudents];
drop Table [genderYear];
drop Table [myStudents];
Thanks
