How to make a transformation after CrossTable?

[michele_nesci]

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.

[marcus_sommer]

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

[michele_nesci]

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.

[michele_nesci]

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.

[stigchel]

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);