4 Replies Latest reply: May 14, 2014 3:56 AM by Toni Kautto RSS

    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

        • Re: Crosstable
          Toni Kautto

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

            • Re: Crosstable

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

               

              Thanks

               

              H

                • 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

                    • Re: Crosstable
                      Toni Kautto

                      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