topic Re: How to make a transformation after CrossTable? in QlikView

How to make a transformation after CrossTable?

michele_nesci — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to make a transformation after CrossTable?

marcus_sommer — Mon, 17 Sep 2018 14:43:07 GMT

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

Re: How to make a transformation after CrossTable?

michele_nesci — Tue, 18 Sep 2018 07:21:39 GMT

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.

Re: How to make a transformation after CrossTable?

michele_nesci — Tue, 18 Sep 2018 07:35:24 GMT

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.

Re: How to make a transformation after CrossTable?

stigchel — Tue, 18 Sep 2018 15:42:26 GMT

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