Crosstable

[#1]

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

[ToniKautto · Accepted solution]

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

[#3]

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

Thanks

H

[#4]

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

[ToniKautto]

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