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
