Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I happily found CrossTable
in Qlik Sense and used it to straighten a dataset containing answers to a survey.
There are several instances of dirty data in the set, which I'd want to clean up after CrossTable
did its magic. They include mappings, if
statements and the like. I couldn't find a way to do so.
In theory, I could repeat all my cleaning on every column, but they are many (about a hundred) and I'll have to do this seven more times for more surveys, with even more to come. Copying all the cleaning code hundreds of times sounds like it is a nightmare, especially if I need to change it somehow later.
How does your table look like after the crosstable and which kind of data should be cleaned/replaced with what? Usually a few statements mostly mappings should be sufficient to correct your data.
- Marcus
Thank you for the reply. I have no problem with writing the statements as is. Mostly I have to trim text and convert numeric results to numbers. The problem is that I see no place to plug a formula.
I have found a workaround: load the cross table as is, then use it to generate a new table with all modifications and finally drop the first one. A minimal example:
[Survey_raw]:
LOAD
CrossTable(Question,Answer_raw)
FROM [lib://results/survey.xlsx]
(ooxml, embedded labels, table is answers);
[Survey]
Load *,
Num#(Answer_raw) as Answer
Resident [Survey_raw];
Drop Table Survey_raw;
Is there a better way? Having to drop the table is a bit of a code smell.
It is not unusual to load the raw data first and then your cleaned data in a new table, especially if you don't need to keep your 'dirty' data. In your small example you do however with the load *, resident. raw. So don't use load * for your resident load. Alternatively you can use something like the below without having to drop a table
CrossTable(QID, Answer,7) Load*,if(Q8>0,Q64) as Q64_new
,if(Q8>0 and Q64=1,if(Q8=1 and Q64=1,1,2)) as Q8_Q64
;
Load *
FROM [lib://results/survey.xlsx]
(ooxml, embedded labels, table is answers);