Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Crosstable

Hi All,

I have a cross table which I am trying to transform to a regular table. I have attached a spreadsheet with some dummy data. Columns A to I need to remain as the regular columns, but columns J to X (in red) should be 2 columns called 'Variable' i.e. the current column heading name and 'Value', which is the actual number.

Any assistance greatly appreciated

H

1 Solution

Accepted Solutions
Employee
Employee

Re: Crosstable

Simply load the fields you want to have from the source, and add Crosstable prefix to the load statement to rotate the data.

CrossTable(Variable, Value, 1)

LOAD

  SampleDetailId,

  Sodium,

  Potassium,

  Urea,

  Glucose,

  Calcium,

  Phosphate,

  Urate,

  Creatinine,

  Bilirubin,

  TotalProtein,

  Albumin,

  ASAT,

  ALP,

  Cholesterol,

  ALAT

FROM Data.xlsx

(ooxml, embedded labels, table is Data);

4 Replies
Employee
Employee

Re: Crosstable

Simply load the fields you want to have from the source, and add Crosstable prefix to the load statement to rotate the data.

CrossTable(Variable, Value, 1)

LOAD

  SampleDetailId,

  Sodium,

  Potassium,

  Urea,

  Glucose,

  Calcium,

  Phosphate,

  Urate,

  Creatinine,

  Bilirubin,

  TotalProtein,

  Albumin,

  ASAT,

  ALP,

  Cholesterol,

  ALAT

FROM Data.xlsx

(ooxml, embedded labels, table is Data);

Not applicable

Re: Crosstable

Thanks for this Tony, but how do I incorporate the other columns A to I?

Thanks

H

Not applicable

Re: Crosstable

Hi Tony,

Figured it out, it was the 1 in "CrossTable(Variable, Value, 1)" that I can tweak. Many thanks for the original answer.

H

Employee
Employee

Re: Crosstable

Exactly, the 3rd parameter in the CrossTable function sets the number of Qualifier field that you want to have. By default this is 1, so it must not be defined if you have one qualifier field.

The table files wizard can also be helpful to generate the required script, or if you want to visually see how the crosstable function affects the data.

1. Script Editor > Table Files...

2. Select target file

3. Make the Type selecitons as required

4. Next > Enable Transformation Steps > Next > Prefixes > Crosstable...

5. Set the required number of Qualifier fields, name the Attribute and Data fields

6. Validate in the Crosstable wizard's preview that the selection matches your data and requirements

7. OK > Finish

8. Confirm that the generate script looks as expected, and reload

Community Browser